MySQL 的rowid 问题

遇到的问题如下:

mysql> create table aa(id int, age int);

mysql> insert into aa values (1,111);

mysql> select _rowid from aa ;

ERROR 1054 (42S22): Unknown column ‘_rowid’ in ‘field list’

解释:

Unique Indexes

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix length. A UNIQUE index permits multiple NULL values for columns that can contain NULL.

If a table has a PRIMARY KEY or UNIQUE NOT NULL index that consists of a single column that has an integer type, you can use _rowid to refer to the indexed column in SELECT statements, as follows:

  • _rowid refers to the PRIMARY KEY column if there is a PRIMARY KEY consisting of a single integer column. If there is a PRIMARY KEY but it does not consist of a single integer column, _rowid cannot be used.
  • Otherwise, _rowid refers to the column in the first UNIQUE NOT NULL index if that index consists of a single integer column. If the first UNIQUE NOT NULL index does not consist of a single integer column, _rowid cannot be used.

来自 <https://dev.mysql.com/doc/refman/5.7/en/create-index.html>

_rowid refers to…说明rowid实际是指向主键/非空唯一索引的指针。select看它的值就是主键/非空唯一索引的值。

另外要注意,如果每列的值都不满足上面两种情况,那么_rowid这个名称就没法使用,不代表这个隐藏列就不存在了。那么这时候又是怎么做的呢:

14.6.2.1 Clustered and Secondary Indexes

Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, you must understand how InnoDB uses the clustered index to optimize the most common lookup and DML operations for each table.

  • When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.
  • If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.
  • If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the 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.

来自https://dev.mysql.com/doc/refman/5.6/en/innodb-index-types.html

原来当没有主键或者非空的唯一索引时,InnoDB会内部地添加一个聚集索引,怪不得没法用_rowid这个名称来做引用了!

所以,姜老师的《MySQL技术内幕 InnoDB存储引擎》P104页的这句话就是错的:若InnoDB表没有定义主键,每行还会增加一个6字节的rowid列。

再来个实验:

mysql> create table aa(id int primary key,dept int, age int);

mysql> insert into aa values (1,11,111);

mysql> SELECT _rowid FROM aa;

+——–+

| _rowid |

+——–+

|      1 |

发表评论

电子邮件地址不会被公开。