假如有这样一个需求，在餐馆叫号系统中，每天叫的号都必须从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);
执行结果如下，可以看到也实现了预期的需求。
