如何防止重复信息进入mysql数据库？

Question

在这个程序中，我创建了一个名为mydata的数据库，创建了一个名为mining的表，我想从列表中读取我的姓名和年龄，如果它不在数据库中，则将其保存在数据库中。但是我遇到了麻烦，我的程序给出了一个错误。

实际上，在编写SQL语法时，如果数据不存在，它会将数据存储在数据库中。我有个问题

mycode：

import mysql.connector

cnx  = mysql.connector.connect(
    host='localhost',
    user = 'root',
    password = '',
    database = 'mydata'
)

c = cnx.cursor()

list_number = ['mobin','ghanbari',15]


for i in list_number:
    c.execute('INSERT INTO mining VALUES ("%s","%s","%s") WHERE NOT EXISTS (SELECT * FROM mining)'%(i,i,i))

请帮我解决这个问题

Answer 1

您需要为不想重复的字段添加唯一索引

下面是一个包含3个字段的示例。您可以使用多个字段

mysql> ALTER TABLE mining ADD UNIQUE INDEX(your_field1, your_field2,your_field3);

并使用INSERT忽略而不是INSERT。这将阻止查询在表中插入相同的数据两次或多次

for i in list_number:
    c.execute('INSERT IGNORE INTO mining VALUES ("%s","%s","%s") )

或执行查询：

SELECT * FROM mining WHERE 
your_field1 = 'mobin' AND 
your_field2 = 'ghanbari' AND 
your_field3 = 15;

在查询插入之前。

如果SELECT查询返回某些内容，则不执行INSERT查询。否则，执行插入查询。

Answer 2

访问mysql

$ mysql -u root -p 

以mysql为例，从命令行创建数据库和表。

mysql> CREATE DATABASE sof;

查询正常，1行受影响(0.00秒)

mysql> use sof;

数据库已更改

mysql> CREATE TABLE mining (
    -> id int NOT NULL AUTO_INCREMENT,
    -> lastname varchar(255) NOT NULL,
    -> firstname varchar(255),
    -> age int,
    -> PRIMARY KEY (id)
    -> );

查询正常，0行受影响(0.01秒)

mysql> show tables;
+---------------+
| Tables_in_sof |
+---------------+
| mining        |
+---------------+

mysql> explain mining;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| lastname  | varchar(255) | NO   |     | NULL    |                |
| firstname | varchar(255) | YES  | MUL | NULL    |                |
| age       | int(11)      | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE mining ADD UNIQUE INDEX(firstname, lastname, age);

查询正常，0行受影响(0.02秒)记录:0重复:0警告:0

mysql> SHOW INDEXES FROM mining;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | 
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
| mining |          0 | PRIMARY   |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               | 
| mining |          0 | firstname |            1 | firstname   | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               | 
| mining |          0 | firstname |            2 | lastname    | A         |           2 |     NULL | NULL   |      | BTREE      |         |               | 
| mining |          0 | firstname |            3 | age         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |                     
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+                     
4 rows in set (0.00 sec)

如果索引可以包含重复项，请注意字段Non_unique 1；如果不能包含重复项，请注意字段0。

现在是脚本：

import mysql.connector

mydb  = mysql.connector.connect(
    host='localhost',
    user = 'root',
    password = '',
    database = 'sof'
)

cursor = mydb.cursor()

listd =[
    {'firstname':'mobin1','lastname':'ghanbari1','age':15},
    {'firstname':'mobin2','lastname':'ghanbari2','age':16},
    {'firstname':'mobin3','lastname':'ghanbari3','age':17},
    {'firstname':'mobin4','lastname':'ghanbari4','age':18}
]

for row in listd:
    sql = "INSERT INTO mining (firstname, lastname , age) VALUES (%s, %s, %s)"
    val = (row['firstname'],row['lastname'], row['age'])

    try:
        cursor.execute(sql, val)
        mydb.commit()
        print(cursor.rowcount, "record inserted for "+row["firstname"])
    except mysql.connector.Error as err:
        print("mysql exception: {}".format(err))
        print(cursor.rowcount, "record inserted for "+row["firstname"])

输出：

1 record inserted for mobin1
1 record inserted for mobin2
1 record inserted for mobin3
1 record inserted for mobin4

如果插入了记录，则在mysql上进行检查

mysql> select * from mining;
+----+-----------+-----------+------+
| id | lastname  | firstname | age  |
+----+-----------+-----------+------+
|  1 | ghanbari1 | mobin1    |   15 |
|  2 | ghanbari2 | mobin2    |   16 |
|  3 | ghanbari3 | mobin3    |   17 |
|  4 | ghanbari4 | mobin4    |   18 |
+----+-----------+-----------+------+


4 rows in set (0.00 sec)

在Python脚本*的列表中添加一些数据

import mysql.connector

mydb  = mysql.connector.connect(
    host='localhost',
    user = 'root',
    password = '',
    database = 'sof'
)

cursor = mydb.cursor()

listd =[
    {'firstname':'mobin1','lastname':'ghanbari1','age':15},
    {'firstname':'mobin2','lastname':'ghanbari2','age':16},
    {'firstname':'mobin3','lastname':'ghanbari3','age':17},
    {'firstname':'mobin4','lastname':'ghanbari4','age':18},    
    {'firstname':'mobin5','lastname':'ghanbari5','age':19},
    {'firstname':'mobin6','lastname':'ghanbari6','age':20},
    {'firstname':'mobin7','lastname':'ghanbari7','age':21}
]

for row in listd:
    sql = "INSERT INTO mining (firstname, lastname , age) VALUES (%s, %s, %s)"
    val = (row['firstname'],row['lastname'], row['age'])

    try:
        cursor.execute(sql, val)
        mydb.commit()
        print(cursor.rowcount, "record inserted for "+row["firstname"])
    except mysql.connector.Error as err:
        print("mysql exception: {}".format(err))
        print(cursor.rowcount, "record inserted for "+row["firstname"])

运行Python脚本的

输出：

mysql exception: 1062 (23000): Duplicate entry 'mobin1-ghanbari1-15' for key 'firstname'
-1 record inserted for mobin1
mysql exception: 1062 (23000): Duplicate entry 'mobin2-ghanbari2-16' for key 'firstname'
-1 record inserted for mobin2
mysql exception: 1062 (23000): Duplicate entry 'mobin3-ghanbari3-17' for key 'firstname'
-1 record inserted for mobin3
mysql exception: 1062 (23000): Duplicate entry 'mobin4-ghanbari4-18' for key 'firstname'
-1 record inserted for mobin4
1 record inserted for mobin5
1 record inserted for mobin6
1 record inserted for mobin7

检查mysql上是否有重复的条目

mysql> select * from mining;
+----+-----------+-----------+------+
| id | lastname  | firstname | age  |
+----+-----------+-----------+------+
|  1 | ghanbari1 | mobin1    |   15 |
|  2 | ghanbari2 | mobin2    |   16 |
|  3 | ghanbari3 | mobin3    |   17 |
|  4 | ghanbari4 | mobin4    |   18 |
| 13 | ghanbari5 | mobin5    |   19 |
| 14 | ghanbari6 | mobin6    |   20 |
| 15 | ghanbari7 | mobin7    |   21 |
+----+-----------+-----------+------+
7 rows in set (0.00 sec)

如您所见，表中没有重复的条目。