在mysql 5.1的文档里面,12.5.13里面有如下文字:If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index. If there is no such index in the table, InnoDB internally generates a clustered index where the rows are ordered by the row ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order. 看到此处,我想oracle里面有row_id,难道mysql的innodb也有了此功能了吗?我就做了一个实例:mysql> create table a2(c1 varchar(10) default null, c2 varchar(20) default null);
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> insert into a2 select '1','2';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into a2 select '2','2';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into a2 select '2','2';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into a2 select '2','2';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into a2 select '2','2';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from a2;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 2 |
| 2 | 2 |
| 2 | 2 |
| 2 | 2 |
| 2 | 2 |
+------+------+
5 rows in set (0.00 sec)
mysql> select rowid,a2.* from a2;
ERROR 1054 (42S22): Unknown column 'rowid' in 'field list'
mysql> select row_id,a2.* from a2;
ERROR 1054 (42S22): Unknown column 'row_id' in 'field list'
mysql>此表a2没有主键也没有unique列,在insert数据的时候按照手册的说明,应该会有一个伪列rowid,但是我用的2个字段rowid和row_id来查询都报错了,查不到,
那么如何鉴定表a2有这个自动的6字节的rowid列呢?
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> insert into a2 select '1','2';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into a2 select '2','2';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into a2 select '2','2';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into a2 select '2','2';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into a2 select '2','2';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from a2;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 2 |
| 2 | 2 |
| 2 | 2 |
| 2 | 2 |
| 2 | 2 |
+------+------+
5 rows in set (0.00 sec)
mysql> select rowid,a2.* from a2;
ERROR 1054 (42S22): Unknown column 'rowid' in 'field list'
mysql> select row_id,a2.* from a2;
ERROR 1054 (42S22): Unknown column 'row_id' in 'field list'
mysql>此表a2没有主键也没有unique列,在insert数据的时候按照手册的说明,应该会有一个伪列rowid,但是我用的2个字段rowid和row_id来查询都报错了,查不到,
那么如何鉴定表a2有这个自动的6字节的rowid列呢?
参考
http://www.flamingspork.com/blog/2009/02/02/row-id-in-mysql-and-drizzle-and-the-engines/
报错了:mysql> select _rowid from a2;
ERROR 1054 (42S22): Unknown column '_rowid' in 'field list'
这个网址上的,说的是有主键id 的才可以select _rowid; 跟手册上说的不一样啊?
手册上说没有主键和唯一键的话,innodb存储引擎会自动弄一个rowid啊。
狼头你说的 仅仅内部可见 是因为 internally generates可以理解成内部生成的,但是我从哪里可以鉴别 仅仅内部可见,外部不可见呢?
在平时INNODB建表的时候我们最好自己确定主键,防止每次插入数据前数据库会去生成row ID。