|
使用AUTO_INCERMENT 来定义- mysql> CREATE TABLE insect
- -> (
- -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
- -> PRIMARY KEY (id),
- -> name VARCHAR(30) NOT NULL, # type of insect
- -> date DATE NOT NULL, # date collected
- -> origin VARCHAR(30) NOT NULL # where collected
- );
- Query OK, 0 rows affected (0.02 sec)
- mysql> INSERT INTO insect (id,name,date,origin) VALUES
- -> (NULL,'housefly','2001-09-10','kitchen'),
- -> (NULL,'millipede','2001-09-10','driveway'),
- -> (NULL,'grasshopper','2001-09-10','front yard');
- Query OK, 3 rows affected (0.02 sec)
- Records: 3 Duplicates: 0 Warnings: 0
- mysql> SELECT * FROM insect ORDER BY id;
- +----+-------------+------------+------------+
- | id | name | date | origin |
- +----+-------------+------------+------------+
- | 1 | housefly | 2001-09-10 | kitchen |
- | 2 | millipede | 2001-09-10 | driveway |
- | 3 | grasshopper | 2001-09-10 | front yard |
- +----+-------------+------------+------------+
- 3 rows in set (0.00 sec)
复制代码 上述代码解析: 以上实例中 创建了数据表 inset ,inset 中的 id 无需指定值可实现自动增长。
获取auto_increment 的值: 使用函数 LAST-INSERT_ID() 函数来获取最后的插入表中的自增列的值。、
帖段PHP代码:- mysql_query ("INSERT INTO insect (name,date,origin)
- VALUES('moth','2001-09-14','windowsill')", $conn_id);
- $seq = mysql_insert_id ($conn_id);
复制代码
重置序列: 对剩下 的数据重新排序- mysql> ALTER TABLE insect DROP id;
- mysql> ALTER TABLE insect
- -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
- -> ADD PRIMARY KEY (id);
复制代码
设置序列的开始值:- ysql> CREATE TABLE insect
- -> (
- -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
- -> PRIMARY KEY (id),
- -> name VARCHAR(30) NOT NULL,
- -> date DATE NOT NULL,
- -> origin VARCHAR(30) NOT NULL
- )engine=innodb auto_increment=100 charset=utf8;
复制代码
或者可以在表创建成功后,通过以下语句来实现- ALTER TABLE t AUTO_INCREMENT = 100;
复制代码
|
|