ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > InnoDB表主键的选择

InnoDB表主键的选择

原创 MySQL 作者：javenzhen 时间：2015-07-15 11:13:23 0 删除 编辑
在《InnoDB存储引擎》书中看到InnoDB表都是有主键的，如果没有显示定义主键，则InnoDB首先判断表中是否有非空的唯一索引，如果有，该列即为主键。如果有多个单一列唯一索引，则按照唯一索引顺序，排在前的为主键。

  1. mysql> show create table t2\\G
  2. *************************** 1. row ***************************
  3.        Table: t2
  4. Create Table: CREATE TABLE `t2` (
  5.   `a` int(11) DEFAULT NULL,
  6.   `b` int(11) NOT NULL,
  7.   `c` int(11) NOT NULL
  8. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  9. 1 row in set (0.00 sec)
在表t2上添加多个唯一索引，不按照列的顺序添加

  1. mysql> alter table t2 add unique (a),add unique (c),add unique (b);
  2. Query OK, 0 rows affected (0.09 sec)
  3. Records: 0 Duplicates: 0 Warnings: 0

  5. mysql> show create table t2\\G
  6. *************************** 1. row ***************************
  7.        Table: t2
  8. Create Table: CREATE TABLE `t2` (
  9.   `a` int(11) DEFAULT NULL,
  10.   `b` int(11) NOT NULL,
  11.   `c` int(11) NOT NULL,
  12.   UNIQUE KEY `c` (`c`),
  13.   UNIQUE KEY `b` (`b`),
  14.   UNIQUE KEY `a` (`a`)
  15. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  16. 1 row in set (0.00 sec)
现在唯一索引的顺序是a，c，b，从建表语句就已经可以看出顺序了，现在唯一索引的顺序变成了c，b，a，插入数据

  1. mysql> insert into t2 (b,c) values (1,2);
  2. Query OK, 1 row affected (0.01 sec)

  4. mysql> insert into t2 (b,c) values (10,20);
  5. Query OK, 1 row affected (0.00 sec)

  7. mysql> insert into t2 (a,b,c) values (10,20,30);
  8. Query OK, 1 row affected (0.00 sec)

  10. mysql> select a,b,c,_rowid from t2;
  11. +------+----+----+--------+
  12. | a | b | c | _rowid |
  13. +------+----+----+--------+
  14. | NULL | 1 | 2 | 2 |
  15. | NULL | 10 | 20 | 20 |
  16. | 10 | 20 | 30 | 30 |
  17. +------+----+----+--------+
  18. 3 rows in set (0.00 sec)
_rowid列是InnoDB存储引擎自动创建的一个6字节大小的指针，此时可看出_rowid是以c列为主键，因选择主键的条件是非空的唯一索引，之后才按照唯一索引的顺序来选择主键。
如果表中无显示主键，也没有非空的唯一索引，那么主键是如何定义的呢？

  1. mysql> create table t3(a int,b int,c int);
  2. Query OK, 0 rows affected (0.06 sec)

  4. mysql> insert into t3 select 1,2,3;
  5. Query OK, 1 row affected (0.01 sec)
  6. Records: 1 Duplicates: 0 Warnings: 0

  8. mysql> insert into t3 select 2,3,4
  9.     -> ;
  10. Query OK, 1 row affected (0.00 sec)
  11. Records: 1 Duplicates: 0 Warnings: 0

  13. mysql> insert into t3 select 3,4,5;
  14. Query OK, 1 row affected (0.00 sec)
  15. Records: 1 Duplicates: 0 Warnings: 0
创建表t3，并插入数据，且数据没有null值，但是列a，b，c都是可为null的列

  1. mysql> select a,b,c,_rowid from t3\\G
  2. ERROR 1054 (42S22): Unknown column \'_rowid\' in \'field list\'
此时表中没有自动生成_rowid列。下面更改b列为not null

  1. mysql> alter table t3 modify b int(11) not null;
  2. Query OK, 0 rows affected (0.10 sec)
  3. Records: 0 Duplicates: 0 Warnings: 0

  5. mysql> show create table t3\\G
  6. *************************** 1. row ***************************
  7.        Table: t3
  8. Create Table: CREATE TABLE `t3` (
  9.   `a` int(11) DEFAULT NULL,
  10.   `b` int(11) NOT NULL,
  11.   `c` int(11) DEFAULT NULL
  12. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  13. 1 row in set (0.00 sec)

  15. mysql> select a,b,c,_rowid from t3\\G
  16. ERROR 1054 (42S22): Unknown column \'_rowid\' in \'field list\'
更改列为not null依然没有自动生成_rowid列。再次为c列添加可为空的唯一索引

  1. mysql> alter table t3 add unique (c);
  2. Query OK, 0 rows affected (0.10 sec)
  3. Records: 0 Duplicates: 0 Warnings: 0

  5. mysql> select a,b,c,_rowid from t3\\G
  6. ERROR 1054 (42S22): Unknown column \'_rowid\' in \'field list\'
  7. mysql> show create table t3\\G
  8. *************************** 1. row ***************************
  9.        Table: t3
  10. Create Table: CREATE TABLE `t3` (
  11.   `a` int(11) DEFAULT NULL,
  12.   `b` int(11) NOT NULL,
  13.   `c` int(11) DEFAULT NULL,
  14.   UNIQUE KEY `c` (`c`)
  15. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  16. 1 row in set (0.00 sec)

  18. mysql> select a,b,c,_rowid from t3\\G
  19. ERROR 1054 (42S22): Unknown column \'_rowid\' in \'field list\'
也就是说如果表中没有显示指定主键，非空且唯一索引是必须条件。
如果表中有一个多列唯一索引，情况又将如何呢？

  1. mysql> alter table t3 modify c int(11) not null;
  2. Query OK, 0 rows affected, 1 warning (0.11 sec)
  3. Records: 0 Duplicates: 0 Warnings: 1

  5. mysql> alter table t3 add unique (b,c);
  6. Query OK, 0 rows affected (0.10 sec)
  7. Records: 0 Duplicates: 0 Warnings: 0

  9. mysql> show create table t3\\G
  10. *************************** 1. row ***************************
  11.        Table: t3
  12. Create Table: CREATE TABLE `t3` (
  13.   `a` int(11) DEFAULT NULL,
  14.   `b` int(11) NOT NULL,
  15.   `c` int(11) NOT NULL,
  16.   UNIQUE KEY `b` (`b`,`c`)
  17. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  18. 1 row in set (0.00 sec)

  20. mysql> select a,b,c,_rowid from t3\\G
  21. ERROR 1054 (42S22): Unknown column \'_rowid\' in \'field list\'
总结：
从我这里的实验可以看出，如果没有显示定义主键，单一列非空的唯一索引即为主键。
那么InnoDB存储引擎中所有表都有主键，该如何去查看呢？

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12219480/viewspace-1733787/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 删除自增长列主键
下一篇： mysql prompt设置
请登录后发表评论 登录
全部评论
javenzhen

注册时间：2011-01-27

  • 博文量
    41
  • 访问量
    124940

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593