Inyección SQL MySQL Uso de secuencias MySQL

Manejo de datos repetidos MySQL

Algunas tablas de MySQL pueden contener registros repetidos, en algunas situaciones permitimos la existencia de datos repetidos, pero a veces también necesitamos eliminar estos datos repetidos.

En esta sección, le presentaremos cómo evitar que los datos de la tabla se repitan y cómo eliminar los datos repetidos de la tabla.

prevenir la aparición de datos repetidos en la tabla

Puede configurar campos específicos en las tablas de MySQL como PRIMARY KEY (clave primaria) o UNIQUE (único) Índice para garantizar la unicidad de los datos.

Vamos a intentar un ejemplo: en la tabla siguiente no hay índice ni clave primaria, por lo que la tabla permite la aparición de múltiples registros repetidos.

CREATE TABLE person_tbl
(
　　　　first_name CHAR(20),
　　　　last_name CHAR(20),
　　　　sex CHAR(10)
);

Si desea establecer que los datos de los campos first_name, last_name en la tabla no puedan repetirse, puede configurar un modo de clave primaria duplicada para establecer la unicidad de los datos. Si ha configurado una clave primaria duplicada, el valor predeterminado de esa clave no puede ser NULL, puede establecer NOT NULL.

CREATE TABLE person_tbl
(
　　　first_name CHAR(20) NOT NULL,
　　　last_name CHAR(20) NOT NULL,
　　　sex CHAR(10),
　　　PRIMARY KEY (last_name, first_name)
);

Si configuramos un índice único, la sentencia SQL no se puede ejecutar con éxito al insertar datos repetidos y lanzará un error.

La diferencia entre INSERT IGNORE INTO y INSERT INTO es que INSERT IGNORE INTO ignora los datos existentes en la base de datos, si la base de datos no tiene datos, inserta nuevos datos, y si ya hay datos, salta esta línea de datos. De esta manera, se puede mantener los datos existentes en la base de datos y lograr la inserción de datos en los huecos.

以下示例使用了 INSERT IGNORE INTO，执行后不会出错，也不会向数据表中插入重复数据：

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
　　　　-> VALUES('Jay', 'Thomas');
Query OK,　1　row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
　　　　-> VALUES('Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

INSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。而 REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。

另一种设置数据的唯一性方法是添加一个 UNIQUE 索引，如下所示：

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

统计重复数据

以下我们将统计表中 first_name 和 last_name的重复记录数：

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

以上查询语句将返回 person_tbl 表中重复的记录数。一般情况下，查询重复的值，请执行以下操作：

确定哪一列包含的值可能会重复。
在列选择列表使用COUNT(*)列出的那些列。
在GROUP BY子句中列出的列。
HAVING子句设置重复数大于1。

过滤重复数据

如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

mysql> SELECT DISTINCT last_name, first_name
　　　　-> FROM person_tbl;

你也可以使用 GROUP BY 来读取数据表中不重复的数据：

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

Eliminar datos repetidos

Si deseas eliminar datos repetidos de la tabla de datos, puedes usar la siguiente sentencia SQL:

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

Por supuesto, también puedes agregar INDEX (índice) y PRIMAY KEY (primaria) de manera sencilla para eliminar registros repetidos en la tabla de datos. El método es el siguiente: