MySQL版本 select version(); +------------+ | version() | +------------+ | 5.7.21-log | +------------+ 1 row in set (0.00 sec)
非空约束为null 并在自增列属性前
create table test_auto_incre(id int null auto_increment,id2 int default null ,key idx_id(id));
show create table test_auto_incre;
CREATE TABLE
test_auto_incre
(id
int(11) NOT NULL AUTO_INCREMENT,id2
int(11) DEFAULT NULL, KEYidx_id
(id
) ) ENGINE=InnoDB DEFAULT CHARSET=utf8insert into test_auto_incre(id2) values(12),(2312); select * from test_auto_incre; +----+------+ | id | id2 | +----+------+ | 1 | 12 | | 2 | 2312 | +----+------+ 2 rows in set (0.00 sec)
非空约束为null 并在自增列属性后
create table test_auto_incre2(id int auto_increment null,id2 int null,key idx_id(id)); Query OK, 0 rows affected (0.02 sec)
非空约束在自增列属性后,不是MySQL的标准建表语句,但建该表没有报错和警告 show create table test_auto_incre2;
CREATE TABLE
test_auto_incre2
(id
int(11) AUTO_INCREMENT,id2
int(11) DEFAULT NULL, KEYidx_id
(id
) ) ENGINE=InnoDB;
插入数据
insert into test_auto_incre2(id2) values(12),(2312); select * from test_auto_incre2; +------+------+ | id | id2 | +------+------+ | NULL | 12 | | NULL | 2312 | +------+------+ 2 rows in set (0.00 sec)
非空约束为not null 并在自增列属性后
create table test_auto_incre2(id int auto_increment not null,id2 int null,key idx_id(id));
show create table test_auto_incre2;
CREATE TABLE
test_auto_incre2
(id
int(11) NOT NULL AUTO_INCREMENT,id2
int(11) DEFAULT NULL, KEYidx_id
(id
) ) ENGINE=InnoDB A
插入数据
insert into test_auto_incre2(id2) values(12),(2312); select * from test_auto_incre2; +----+------+ | id | id2 | +----+------+ | 1 | 12 | | 2 | 2312 | +----+------+
MySQL标准建表语法