MySQL复合主键如何设置自增长?ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

假如有这样一个需求,在餐馆叫号系统中,每天叫的号都必须从1号开始,那么如何设计一张表,来保存每天的叫号记录呢?

比如要求保存的数据如下:

叫号日期

叫号号码

手机号

就餐人数

CALL_DATE

CALL_NUMBER

PHONE

PEOPLE_COUNT

2015/1/1

1

15888888888

1

2015/1/1

2

15888888889

8

2015/1/1

3

15888888890

8

2015/1/1

4

15888888891

2

2015/1/1

5

15888888892

8

2015/1/1

6

15888888893

4

2015/1/1

7

15888888894

2

2015/1/2

1

15888888895

3

2015/1/2

2

15888888896

7

2015/1/2

3

15888888897

6

2015/1/2

4

15888888898

3

2015/1/2

5

15888888899

2

2015/1/2

6

15888888900

8

2015/1/2

7

15888888901

6

2015/1/2

8

15888888902

10

2015/1/2

9

15888888903

3

2015/1/2

10

15888888904

2

2015/1/2

11

15888888905

1

2015/1/3

1

15888888906

5

2015/1/3

2

15888888907

2

2015/1/3

3

15888888908

5

2015/1/3

4

15888888909

7

2015/1/3

5

15888888910

1

2015/1/3

6

15888888911

1

那么,可以设计一张如下表来保存数据:

CREATE TABLE CALL_NUMBER_RECORD(
CALL_DATE DATE NOT NULL,
CALL_NUMBER INT NOT NULL,
PHONE VARCHAR(20) NOT NULL,
PEOPLE_COUNT SMALLINT NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk COMMENT='叫号记录表';

那么这张表的PK,就可以设定为CALL_DATE+CALL_NUMBER,这很简单。可如何能让表中的数据,随着日期的增加而自动增长呢?很明显,我们可以将CALL_NUMBER字段设置为自增类型。像下面这样:

CREATE TABLE CALL_NUMBER_RECORD(
CALL_DATE DATE NOT NULL,
CALL_NUMBER INT NOT NULL AUTO_INCREMENT,
PHONE VARCHAR(20) NOT NULL,
PEOPLE_COUNT SMALLINT NOT NULL,
PRIMARY KEY(CALL_DATE,CALL_NUMBER)
) ENGINE=MyISAM DEFAULT CHARSET=gbk COMMENT='叫号记录表';
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-01','15888888888',1);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-01','15888888889',8);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-01','15888888890',8);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-01','15888888891',2);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-01','15888888892',8);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-01','15888888893',4);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-01','15888888894',2);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','15888888895',3);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','15888888896',7);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','15888888897',6);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','15888888898',3);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','15888888899',2);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','15888888900',8);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','15888888901',6);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','15888888902',10);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','15888888903',3);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','15888888904',2);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','15888888905',1);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-03','15888888906',5);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-03','15888888907',2);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-03','15888888908',5);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-03','15888888909',7);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-03','15888888910',1);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,PHONE,PEOPLE_COUNT) VALUES('2015-01-03','15888888911',1);

将上面表格中的数据使用INSERT语句插入后,表中的数据如下:

需求顺利实现!

不过这里面有几个疑问,需要进一步探讨:

1、表使用的MyISAM存储引擎,如果使用InnoDB存储引擎呢?

答案是,直接报错了!这是因为InnoDB引擎必须有一个独立的唯一索引。

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

CREATE TABLE CALL_NUMBER_RECORD(
CALL_DATE DATE NOT NULL,
CALL_NUMBER INT NOT NULL AUTO_INCREMENT,
PHONE VARCHAR(20) NOT NULL,
PEOPLE_COUNT SMALLINT NOT NULL,
PRIMARY KEY(CALL_DATE,CALL_NUMBER)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='叫号记录表';

像下面在AUTO——INCREMENT字段后加一个UNIQUE约束后,执行通过:

CREATE TABLE CALL_NUMBER_RECORD(
CALL_DATE DATE NOT NULL,
CALL_NUMBER INT NOT NULL AUTO_INCREMENT UNIQUE,
PHONE VARCHAR(20) NOT NULL,
PEOPLE_COUNT SMALLINT NOT NULL,
PRIMARY KEY(CALL_DATE,CALL_NUMBER)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='叫号记录表';

但是,虽然表创建成功了,可并没有满足我们的需求。因为这会导致CALL_NUMBER字段中不会出现重复值,也就是说,即使CALL_DATE不一样,CALL_NUMBER也不会重新从1开始累计。

所以,如果想实现最开始的需求,就必须使用MyISAM存储引擎。

2、如果在定义主键时,将CALL_NUMBER写在第一位,也就是写在CALL_DATE字段的前面,那么数据插入后,是什么效果呢?

答案是,CALL_NUMBER也不会按日期,重新从1开始。

3、如果组合主键有3个字段呢?也就是说,CALL_NUMBER前面有两个字段,结果是什么样呢?

假设这一家连锁餐馆,所有分店的叫号都记录在这张表里,那么主键就需要加一个分店的字段:

CREATE TABLE CALL_NUMBER_RECORD(
CALL_DATE DATE NOT NULL,
BRANCH VARCHAR(100) NOT NULL,
CALL_NUMBER INT NOT NULL AUTO_INCREMENT,
PHONE VARCHAR(20) NOT NULL,
PEOPLE_COUNT SMALLINT NOT NULL,
PRIMARY KEY(CALL_DATE,BRANCH,CALL_NUMBER)
) ENGINE=MyISAM DEFAULT CHARSET=gbk COMMENT='叫号记录表';
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-01','分店1','15888888888',1);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-01','分店1','15888888889',8);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-01','分店1','15888888890',8);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-01','分店1','15888888891',2);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-01','分店1','15888888892',8);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-01','分店2','15888888893',4);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-01','分店2','15888888894',2);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','分店1','15888888895',3);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','分店1','15888888896',7);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','分店1','15888888897',6);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','分店1','15888888898',3);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','分店1','15888888899',2);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','分店1','15888888900',8);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','分店2','15888888901',6);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','分店2','15888888902',10);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','分店2','15888888903',3);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','分店2','15888888904',2);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-02','分店2','15888888905',1);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-03','分店1','15888888906',5);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-03','分店1','15888888907',2);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-03','分店1','15888888908',5);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-03','分店1','15888888909',7);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-03','分店1','15888888910',1);
INSERT INTO CALL_NUMBER_RECORD(CALL_DATE,BRANCH,PHONE,PEOPLE_COUNT) VALUES('2015-01-03','分店2','15888888911',1);

执行结果如下,可以看到也实现了预期的需求。

【版权说明:仅允许非商业转载且请注明出处:Mac私塾 网址:http://macsishu.com】

发表评论

邮箱地址不会被公开。