MySQL->AUTO_INCREMENT[20180516]

MySQL表格中自增长主键AUTO_INCREMENT使用，实现序列的最简单的方式

创建一个AUTO_INCREMENT自增的表

mysql> create table seq_test(
    -> id int  not null auto_increment,
    -> primary key(id),
    -> name varchar(30) not null,
    -> date date not null,
    -> origin varchar(30) not null
    -> )
    -> ;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into seq_test(id,name,date,origin)
    -> values(null,'Alex','2018-01-01','test'),
    -> (null,'Frank','2018-05-01','test'),
    -> (null,'Fred','2018-05-01','test'),
    -> (null,'Gxxx','2018-05-01','PCQ')
    -> ;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from seq_test;
+----+-------+------------+--------+
| id | name  | date       | origin |
+----+-------+------------+--------+
|  1 | Alex  | 2018-01-01 | test   |
|  2 | Frank | 2018-05-01 | test   |
|  3 | Fred  | 2018-05-01 | test   |
|  4 | Gxxx  | 2018-05-01 | PCQ    |
+----+-------+------------+--------+
4 rows in set (0.00 sec)
 

PHP中使用mysqli_insert_id()函数来获取当前插入的序列值

[root@t-xi-oracle01 html]# cat mysqli_select_seq.php
<?php
$dbhost='localhost';
$dbuser='root';
$dbpass='mysql';
$conn=mysqli_connect($dbhost,$dbuser,$dbpass);
if(! $conn)
{
die('Database Connect Failure.' .mysqli_error($conn));

}
echo 'Database Connect Successful.<br />';

mysqli_query($conn,"set names utf8");

$sql_insert='insert into seq_test(name,date,origin) values("Xi","2018-05-16","PCQ")';

mysqli_select_db($conn,'runoob');

mysqli_query($conn,$sql_insert);

$seq = mysqli_insert_id($conn);

$sql = "select * from seq_test where id= $seq ";
$retval= mysqli_query($conn,$sql);

if (! $retval)
{
die('Select Data Failure.' .mysqli_error($conn));
}

echo '<h3>Show Data List</h3>';
echo '<table border="1"> <tr><td>ID</td><td>Name</td><td>Date</td><td>Origin</td><tr>';

while($row=mysqli_fetch_array($retval,MYSQLI_NUM))
{
echo "<tr><td>{$row[]}</td>".
        "<td>{$row[]}</td>".
        "<td>{$row[]}</td>".
        "<td>{$row[]}</td>".
        "</tr>";

}
echo '</table>';
mysqli_free_result($retval);
mysqli_close($conn);

?>

对现有AUTO_INCREMENT列进行重置

alter table seq_test drop id;

alter table seq_test add id int not null auto_increment first,add primary key (id);

为AUTO_INCREMENT给予起始值

    从序列100数值开始

create table seq_test02
(
id int 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;

insert into seq_test02(name,date,origin) select name,date,origin from seq_test;
truncate table seq_test02;
 

    从序列500数值开始

alter table seq_test02 auto_increment= 500;

insert into seq_test02(name,date,origin) select name,date,origin from seq_test limit 10;

mysql> select * from seq_test02;
+-----+-------+------------+--------+
| id  | name  | date       | origin |
+-----+-------+------------+--------+
| 500 | Alex  | 2018-01-01 | test|
| 501 | Frank | 2018-05-01 | test |
| 502 | Fred  | 2018-05-01 | test |
| 503 | Gxxx  | 2018-05-01 | PCQ    |
| 504 | Xi    | 2018-05-16 | PCQ    |
| 505 | Xi    | 2018-05-16 | PCQ    |
| 506 | Xi    | 2018-05-16 | PCQ    |
| 507 | Xi    | 2018-05-16 | PCQ    |
| 508 | Xi    | 2018-05-16 | PCQ    |
| 509 | Xi    | 2018-05-16 | PCQ    |
+-----+-------+------------+--------+
10 rows in set (0.00 sec)