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)

巴特西

MySQL->AUTO_INCREMENT[20180516]

最新文章

热门文章