MariaDB5.5版本测试rowid特性--笔记
ORACL的表有rowid列其实MySQL中也是存在类似rowid，名字叫 _rowid ，但它是一个非空唯一列的别名，不同情况下指向的不同列，并且有_rowid列不存在的情况，_rowid显示条件必须时候有数字类型主键或唯一键并且是int not null
存在_rowid必要条件：
①当表存在integer类型的单字段主键，_rowid指向的就是当前主键字段
②表不存在主键，但是有integer类型的唯一性单字段索引并且not null，_rowid指向的就是当前该字段。
不存在_rowid情况：
①表的主键是联合主键，多个主键字段。
②表主键是单字段，但是是字符串类型。
③表不存在主键，但是有唯一性单字段索引，但是该字段不是integer类型。
④普通索引int即使是not null也不支持显示_rowid
详细测试验证过程：
MariaDB [(none)]> create database db_test;
Query OK, 1 row affected (0.04 sec)
MariaDB [(none)]> use db_test;
Database changed
MariaDB [db_test]>
1、创建int类型主键表
create table tbl_primary(id int(8) not null primary key,name varchar(10)) engine=innodb;
insert into tbl_primary values(1,'a'),(2,'b'),(3,'c');
MariaDB [db_test]> create table tbl_primary(id int(8) not null primary key,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
MariaDB [db_test]> show create table tbl_primary\G
*************************** 1. row ***************************
Table: tbl_primary
Create Table: CREATE TABLE `tbl_primary` (
`id` int(8) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MariaDB [db_test]> insert into tbl_primary values(1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [db_test]> select * from tbl_primary;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
3 rows in set (0.00 sec)
MariaDB [db_test]>
MariaDB [db_test]> select rowid from tbl_primary;
ERROR 1054 (42S22): Unknown column 'rowid' in 'field list'
MariaDB [db_test]> select _rowid from tbl_primary;
+--------+
| _rowid |
+--------+
| 1 |
| 2 |
| 3 |
+--------+
3 rows in set (0.00 sec)
MariaDB [db_test]> select _rowid,id,name from tbl_primary;
+--------+----+------+
| _rowid | id | name |
+--------+----+------+
| 1 | 1 | a |
| 2 | 2 | b |
| 3 | 3 | c |
+--------+----+------+
3 rows in set (0.00 sec)
MariaDB [db_test]>
2、创建int类型唯一键表
create table tbl_unique(id int(8) not null unique key,name varchar(10)) engine=innodb;
insert into tbl_unique values(1,'zhangsan'),(2,'lisi'),(3,'wanger');
MariaDB [db_test]> create table tbl_unique(id int(8) not null unique key,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
MariaDB [db_test]>
MariaDB [db_test]> insert into tbl_unique values(1,'zhangsan'),(2,'lisi'),(3,'wanger');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [db_test]> show create table tbl_unique\G
*************************** 1. row ***************************
Table: tbl_unique
Create Table: CREATE TABLE `tbl_unique` (
`id` int(8) NOT NULL,
`name` varchar(10) DEFAULT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MariaDB [db_test]>
MariaDB [db_test]> select * from tbl_unique;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wanger |
+----+----------+
3 rows in set (0.00 sec)
MariaDB [db_test]>
MariaDB [db_test]> select rowid,id,name from tbl_unique;
ERROR 1054 (42S22): Unknown column 'rowid' in 'field list'
MariaDB [db_test]>
MariaDB [db_test]> select _rowid,id,name from tbl_unique;
+--------+----+----------+
| _rowid | id | name |
+--------+----+----------+
| 1 | 1 | zhangsan |
| 2 | 2 | lisi |
| 3 | 3 | wanger |
+--------+----+----------+
3 rows in set (0.01 sec)
MariaDB [db_test]>
3、int类型唯一键表，如果是null是不支持显示查询_rowid
MariaDB [db_test]> alter table tbl_unique modify column `id` int(8);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [db_test]> show create table tbl_unique\G
*************************** 1. row ***************************
Table: tbl_unique
Create Table: CREATE TABLE `tbl_unique` (
`id` int(8) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MariaDB [db_test]> select _rowid,id,name from tbl_unique;
ERROR 1054 (42S22): Unknown column '_rowid' in 'field list'
MariaDB [db_test]>
MariaDB [db_test]> alter table tbl_unique modify column `id` int(8) not null;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [db_test]> show create table tbl_unique\G
*************************** 1. row ***************************
Table: tbl_unique
Create Table: CREATE TABLE `tbl_unique` (
`id` int(8) NOT NULL,
`name` varchar(10) DEFAULT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MariaDB [db_test]> select _rowid,id,name from tbl_unique;
+--------+----+----------+
| _rowid | id | name |
+--------+----+----------+
| 1 | 1 | zhangsan |
| 2 | 2 | lisi |
| 3 | 3 | wanger |
+--------+----+----------+
3 rows in set (0.00 sec)
MariaDB [db_test]>
4、int类型not null并设置无索引表
create table tbl_int_noindex(id int(8) not null ,age int(2) not null, name varchar(10)) engine=innodb;
insert into tbl_int_noindex values(1,18,'zhangsan'),(2,38,'lisi'),(3,88,'wanger');
并验证增加唯一索引是否支持_rowid
alter table table tbl_int_noindex add unique index udx_aget (age);
验证结果表明后期增加not null唯一键是支持_rowid
下面改为普通索引虽然是not null但也不支持_rowid
alter table tbl_int_noindex drop key udx_aget
alter table tbl_int_noindex add index idx_aget (age);
MariaDB [db_test]> alter table tbl_int_noindex add unique index udx_aget (age);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [db_test]> show create table tbl_int_noindex\G
*************************** 1. row ***************************
Table: tbl_int_noindex
Create Table: CREATE TABLE `tbl_int_noindex` (
`id` int(8) NOT NULL,
`age` int(2) NOT NULL,
`name` varchar(10) DEFAULT NULL,
UNIQUE KEY `udx_aget` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MariaDB [db_test]>
MariaDB [db_test]> select _rowid,id,age,name from tbl_int_noindex;
+--------+----+-----+----------+
| _rowid | id | age | name |
+--------+----+-----+----------+
| 18 | 1 | 18 | zhangsan |
| 38 | 2 | 38 | lisi |
| 88 | 3 | 88 | wanger |
+--------+----+-----+----------+
3 rows in set (0.00 sec)
MariaDB [db_test]>
MariaDB [db_test]> create table tbl_int_noindex(id int(8) not null ,age int(2) not null, name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.06 sec)
MariaDB [db_test]> show create table tbl_int_noindex\G
*************************** 1. row ***************************
Table: tbl_int_noindex
Create Table: CREATE TABLE `tbl_int_noindex` (
`id` int(8) NOT NULL,
`age` int(2) NOT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MariaDB [db_test]> insert into tbl_int_noindex values(1,18,'zhangsan'),(2,38,'lisi'),(3,88,'wanger');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [db_test]> select * from tbl_int_noindex;
+----+-----+----------+
| id | age | name |
+----+-----+----------+
| 1 | 18 | zhangsan |
| 2 | 38 | lisi |
| 3 | 88 | wanger |
+----+-----+----------+
3 rows in set (0.00 sec)
MariaDB [db_test]>
MariaDB [db_test]> select _rowid from tbl_int_noindex;
ERROR 1054 (42S22): Unknown column '_rowid' in 'field list'
MariaDB [db_test]>
MariaDB [db_test]> alter table tbl_int_noindex add index idx_aget (age);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [db_test]> show create table tbl_int_noindex\G
*************************** 1. row ***************************
Table: tbl_int_noindex
Create Table: CREATE TABLE `tbl_int_noindex` (
`id` int(8) NOT NULL,
`age` int(2) NOT NULL,
`name` varchar(10) DEFAULT NULL,
KEY `idx_aget` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MariaDB [db_test]> select _rowid,id,age,name from tbl_int_noindex;
ERROR 1054 (42S22): Unknown column '_rowid' in 'field list'
MariaDB [db_test]>
5、主键是联合int主键
复合主键也不支持显示_rowid
create table tbl_int_complex_primary(id int(8) not null ,age int(2) not null, name varchar(10),primary key(id,age)) engine=innodb;
insert into tbl_int_complex_primary values(1,16,'zhangsan'),(2,18,'lisi'),(3,26,'wanger');
MariaDB [db_test]> create table tbl_int_complex_primary(id int(8) not null ,age int(2) not null, name varchar(10),primary key(id,age)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
MariaDB [db_test]> show create table tbl_int_complex_primary\G
*************************** 1. row ***************************
Table: tbl_int_complex_primary
Create Table: CREATE TABLE `tbl_int_complex_primary` (
`id` int(8) NOT NULL,
`age` int(2) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MariaDB [db_test]> insert into tbl_int_complex_primary values(1,16,'zhangsan'),(2,18,'lisi'),(3,26,'wanger');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [db_test]> select * from tbl_int_complex_primary;
+----+-----+----------+
| id | age | name |
+----+-----+----------+
| 1 | 16 | zhangsan |
| 2 | 18 | lisi |
| 3 | 26 | wanger |
+----+-----+----------+
3 rows in set (0.00 sec)
MariaDB [db_test]> select _rowid from tbl_int_complex_primary;
ERROR 1054 (42S22): Unknown column '_rowid' in 'field list'
MariaDB [db_test]>
6、创建字符类型主键表不支持显示查询_rowid
create table tbl_primary_char(id char(8) primary key,name varchar(10)) engine=innodb;
insert into tbl_primary_char values('a','beijing'),('b','chengdu'),('c','shenzheng');
create table tbl_primary_varchar(id varchar(8) primary key,name varchar(10)) engine=innodb;
insert into tbl_primary_varchar values('e','beijing'),('f','chengdu'),('j','shenzheng');
MariaDB [db_test]> create table tbl_primary_char(id char(8) primary key,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
MariaDB [db_test]> show create table tbl_primary_char\G
*************************** 1. row ***************************
Table: tbl_primary_char
Create Table: CREATE TABLE `tbl_primary_char` (
`id` char(8) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MariaDB [db_test]>
MariaDB [db_test]> insert into tbl_primary_char values('a','beijing'),('b','chengdu'),('c','shenzheng');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [db_test]> select * from tbl_primary_char;
+----+-----------+
| id | name |
+----+-----------+
| a | beijing |
| b | chengdu |
| c | shenzheng |
+----+-----------+
3 rows in set (0.00 sec)
MariaDB [db_test]> select _rowid,id,name from tbl_primary_char;
ERROR 1054 (42S22): Unknown column '_rowid' in 'field list'
MariaDB [db_test]>
MariaDB [db_test]> create table tbl_primary_varchar(id varchar(8) primary key,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
MariaDB [db_test]> show create table tbl_primary_varchar\G
*************************** 1. row ***************************
Table: tbl_primary_varchar
Create Table: CREATE TABLE `tbl_primary_varchar` (
`id` varchar(8) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MariaDB [db_test]> insert into tbl_primary_varchar values('e','beijing'),('f','chengdu'),('j','shenzheng');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [db_test]> select * from tbl_primary_varchar;
+----+-----------+
| id | name |
+----+-----------+
| e | beijing |
| f | chengdu |
| j | shenzheng |
+----+-----------+
3 rows in set (0.00 sec)
MariaDB [db_test]> select _rowid,id,name from tbl_primary_varchar;
ERROR 1054 (42S22): Unknown column '_rowid' in 'field list'
MariaDB [db_test]>
