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;

使用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;