Google Analytics的ID一般形如:UA-18xxxxx-1,UA-18xxxxx-4,
我们可以将它分成两段来看:字段1:UA-18XXXXX是指帐户的ID;字段2:字段1后附加的1、4,刚分别是指该帐户名下的两个网站。有一段时间很
纠结这样的ID怎么去维护,直到最近查阅到Mysql手册才发现,原来Mysql原生就很好的支持了这种含自动增长字段的复合主键,所以字段2是存储引擎
自动计算出来的。计算方式:MAX(auto_increment_column) + 1 WHERE prefix=given-prefix 。
不多说,直接看SQL:
mysql> CREATE TABLE `_test` (
-> `c1` varchar(20) NOT NULL DEFAULT '', -- 主键一:varchar
-> `c2` int(11) NOT NULL AUTO_INCREMENT, -- 主键二:自增序列
-> `c3` varchar(255) ,
-> PRIMARY KEY (`c1`,`c2`) --
注意这里的先后顺序
,InnoDB引擎这么写会报错。如果改成PRIMARY KEY(c2, c1)则下面插入后c2字段是:1 2 3 4 5 和InnoDB引擎一致 -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO `_test` (`c1`,`c3`) VALUES ('AAA','Robin Home'),
-> ('AAA','Robin Blog'),
-> ('AAA','Robin Resume'),
-> ('BBB','Lily Home'),
-> ('BBB','Lily Blog');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM `_test`;
+-----+----+--------------+
| c1 | c2 | c3 |
+-----+----+--------------+
| AAA | 1 | Robin Home |
| AAA | 2 | Robin Blog |
| AAA | 3 | Robin Resume |
| BBB | 1 | Lily Home |
| BBB | 2 | Lily Blog |
+-----+----+--------------+
5 rows in set (0.00 sec)
mysql>
值得注意的是,MySQL的这种特性只适用于MyISAM和BDB引擎。
参考资料:
- 使用AUTO_INCREMENT ,MySQL手册
作者:
本文链接:
PS:1.
插入语句时,自增列的值为NULL。
2.创建表格后添加: alter table test add c1 varchar(20) auto_increment primary key
自增字段,一定要设置为primary key. 3.建表语句:
create table test (c1 varchar(20) not null default '', c2 int not null auto_increment, c3 varchar(255), primary key(c1,c2))engine=MyISAM charset=utf8;
insert into test values('AA', null, 'home'),('AA', null, 'blog'), ('AA', null, 'resume'), ('BB', null, 'lily'), ('BB', null, 'blog');
4.
转贴的:
CREATE TABLE PLAYERS
(PLAYERNO INTEGER NOT NULL PRIMARY KEY,
NAME CHAR(15) NOT NULL,
INITIALS CHAR(3) NOT NULL,
BIRTH_DATE DATE,
*** CHAR(1) NOT NULL
CHECK(*** IN ('M','F')),
JOINED SMALLINT NOT NULL
CHECK(JOINED > 1969) ,
STREET CHAR(30) NOT NULL,
HOUSENO CHAR(4),
POSTCODE CHAR(6) CHECK(POSTCODE LIKE '______'),
TOWN CHAR(10) NOT NULL,
PHONENO CHAR(13),
LEAGUENO CHAR(4))
;
CREATE TABLE TEAMS
(TEAMNO INTEGER NOT NULL PRIMARY KEY,
PLAYERNO INTEGER NOT NULL,
DIVISION CHAR(6) NOT NULL
CHECK(DIVISION IN ('first','second')),
FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))
;
CREATE TABLE MATCHES
(MATCHNO INTEGER NOT NULL PRIMARY KEY,
TEAMNO INTEGER NOT NULL,
PLAYERNO INTEGER NOT NULL,
WON SMALLINT NOT NULL
CHECK(WON BETWEEN 0 AND 3),
LOST SMALLINT NOT NULL
CHECK(LOST BETWEEN 0 AND 3),
FOREIGN KEY (TEAMNO) REFERENCES TEAMS (TEAMNO),
FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))
;
CREATE TABLE PENALTIES
(PAYMENTNO INTEGER NOT NULL PRIMARY KEY,
PLAYERNO INTEGER NOT NULL,
PAYMENT_DATE DATE NOT NULL
CHECK(PAYMENT_DATE >= DATE('1969-12-31')),
AMOUNT DECIMAL(7,2) NOT NULL
CHECK (AMOUNT > 0),
FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))
;
CREATE TABLE COMMITTEE_MEMBERS
(PLAYERNO INTEGER NOT NULL,
BEGIN_DATE DATE NOT NULL,
END_DATE DATE,
POSITION CHAR(20),
PRIMARY KEY (PLAYERNO, BEGIN_DATE),
FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO),
CHECK(BEGIN_DATE < END_DATE),
CHECK(BEGIN_DATE >= DATE('1990-01-01')))
阅读(1373) | 评论(0) | 转发(0) |