mysql开发细节（使用自动递增序列以及处理重复数据） _mysql自动递增序列

在上一章mysql教程中我们讨论了mysql的sql注入，本章中我们开始讨论mysql的一些开发细节：使用自动递增序列以及处理重复数据。
一、使用自动递增序列序列是一组整数1、2、3、…按特定需求的顺序生成的，序列在数据库中经常使用，因为许多应用程序要求表中的每一行都包含一个惟一的值，而序列提供了生成这些值的简单方法。
1、使用AUTO_INCREMENT列
MySQL中使用序列最简单的方法是将一个列定义为AUTO_INCREMENT，其余的事情留给MySQL去处理。
请尝试以下示例创建一个表，然后它将在这个表中插入一些不需要提供记录ID的行，因为它是由MySQL自动递增的。

mysql> create table sample -> ( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (id), -> name VARCHAR(30) NOT NULL, -> date DATE NOT NULL, -> origin VARCHAR(30) NOT NULL ); mysql> insert into sample (id,name,date,origin) values -> (NULL,'A','94','q'), -> (NULL,'B','95','e'), -> (NULL,'C','96','t'); mysql> select * from sample order by id; +----+-------------+------------+------------+ | id |name|date|origin| +----+-------------+------------+------------+ |1 |A| 94 |q| |2 |B| 95 |e| |3 | C | 96 | t | +----+-------------+------------+------------+

2、获取AUTO_INCREMENT值
LAST_INSERT_ID()是一个SQL函数，可以在任何知道如何发出SQL语句的客户机中使用它，否则PERL和PHP脚本需要提供专用函数来检索最后一条记录的自动递增值。
PERL例子：
使用mysql_insertid属性获得查询生成的AUTO_INCREMENT值，下面的示例通过数据库句柄引用它。

$dbh->do ("insert into sample (name,date,origin) VALUES('D','98','p')"); my $seq = $dbh->{mysql_insertid};

PHP例子：
执行生成AUTO_INCREMENT值的查询后，可以通过调用mysql_insert_id()命令检索该值。

mysql_query ("insert into sample (name,date,origin) VALUES('E','99','o')", $conn_id); $seq = mysql_insert_id ($conn_id);

3、重新编号已存在的序列
可能会出现这样的情况：你从表中删除了许多记录并需要重新排序所有记录，这可以通过一个简单的技巧来实现，但是如果你的表与另一个表有连接，那么你应该非常小心地这样做。
如果你确定对AUTO_INCREMENT列的重新排序是不可避免的，那么方法是从表中删除该列，然后再次添加它。

mysql> alter table sample drop id; mysql> alter table sample -> add id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, -> add PRIMARY KEY (id);

从一个特定的值开始序列
默认情况下MySQL将从1开始排序，但是你也可以在创建表时指定任何其他数字。
下面的程序是一个示例，它展示了MySQL如何从100开始排序。

mysql> create table sample -> ( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100, -> PRIMARY KEY (id), -> name VARCHAR(30) NOT NULL, -> date DATE NOT NULL, -> origin VARCHAR(30) NOT NULL );

或者你可以创建表然后使用alter table命令设置初始序列值。
二、mysql处理重复数据我们可能需要标识重复的记录并将它们从表中删除，现在我们将描述如何防止表中重复记录的出现，以及如何删除已经存在的重复记录。
1、防止表中出现重复项
我们可以使用具有适当字段的表的主键或惟一索引来停止重复记录，下面的例子不包含索引或主键因而运行 first_name和last_name重复。

create table sample ( first_name CHAR(20), last_name CHAR(20), sex CHAR(10) );

为了防止在此表中创建具有相同名和姓值的多个记录，请在其定义中添加一个主键。

create table sample ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name) );

如果将一条记录插入表中，而该表与定义该索引的一列或多列中的现有记录重复，如果记录是重复的那么IGNORE关键字告诉MySQL放弃它而不产生错误。

mysql> insert IGNORE into sample (last_name, first_name) -> VALUES( 'A', 'B'); mysql> insert IGNORE into sample (last_name, first_name) -> VALUES( 'A', 'B');

使用REPLACE命令而不是INSERT命令，如果记录是新的它就像INSERT一样被插入，如果是副本则新记录将替换旧记录。

mysql> REPLACE INTO person_tbl (last_name, first_name) -> VALUES( 'Ajay', 'Kumar'); mysql> REPLACE INTO person_tbl (last_name, first_name) -> VALUES( 'Ajay', 'Kumar');

应根据希望实现的复制处理行为选择“INSERT IGNORE和REPLACE”命令，INSERT IGNORE命令保留第一组复制的记录，丢弃其余的记录，REPLACE命令保留最后一组重复项，并擦除任何先前的重复项。强制惟一性的另一种方法是向表添加惟一索引，而不是主键。

CREATE TABLE sample ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10) UNIQUE (last_name, first_name) );

2、计算和识别重复项
下面的查询将对表中具有first_name和last_name的重复记录进行计数。

mysql> SELECT COUNT(*) as repetitions, last_name, first_name -> FROM sample -> GROUP BY last_name, first_name -> HAVING repetitions > 1;

检查重复的值集请遵循以下步骤。

确定可能包含重复的值的列。
使用COUNT(*)列出这样的列。
列出GROUP BY子句中的列。
使用HAVING子句和组计数大于1来消除唯一值。

3、从查询结果中消除重复
可以使用DISTINCT命令和SELECT语句查找表中可用的惟一记录。

mysql> SELECT DISTINCT last_name, first_name -> FROM sample -> ORDER BY last_name;

【mysql开发细节（使用自动递增序列以及处理重复数据）】使用DISTINCT命令的另一种方法是添加一个GROUP BY子句，该子句指定要选择的列的名称，这样做的效果是删除重复项，并只选择指定列中惟一的值组合。

mysql> SELECT last_name, first_name -> FROM sample -> GROUP BY (last_name, first_name);

4、使用表替换删除重复项

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex -> FROM sample; -> GROUP BY (last_name, first_name); mysql> DROP TABLE sample; mysql> ALTER TABLE tmp RENAME TO sample;