如果不存在则插入到 MYSQL 数据库中 [英] Insert into MYSQL Database if not exists

问题描述

我正在尝试将客户添加到我创建的 MySQL 数据库中.每当有人在网上商店订购商品时，客户就会被添加到数据库中(我不想重复).这是我的php代码:

I'm trying to add a customer to the MySQL database I created. Whenever somebody orders an item on the online store, the customer is added to the database (I dont want duplicates). Here is my php code:

$sqlInsert = "INSERT INTO Customers (FirstName, Address, Phone)
VALUES (".$userName.",".$address.",".$phone.")";

if(mysqli_query($conn, $sqlInsert)) {
    echo "new member registered successfully!";
} else {
    echo "Error: " . $sqlInsert . "<br>" . $mysqli_error($conn);
}

我研究了诸如 INSERT INTO... WHERE NOT EXISTS 之类的查询.但我不明白我的情况的语法，也不知道它是否有效.这是我的 MYSQL 客户表代码:

I have looked into queries such as INSERT INTO... WHERE NOT EXISTS. But I don't understand the syntax for my case, and don't know if it would work. here is my MYSQL customer table code:

CREATE TABLE IF NOT EXISTS Customers (
PersonID INT(11) NOT NULL AUTO_INCREMENT, 
Email VARCHAR(100),
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100),
City VARCHAR(90),
Zip INT(10),
CustomerState VARCHAR(50),
Address VARCHAR(200),
Country VARCHAR(20),
Phone VARCHAR(50) NOT NULL,
PRIMARY KEY (PersonID)
);

推荐答案

INSERT INTO  Customers (FirstName, Address, Phone) 
SELECT * FROM (SELECT '$firstName', '$address', '$phone') AS tmp
WHERE NOT EXISTS (
    SELECT FirstName from Customers WHERE FirstName= '$firstName'
)   LIMIT 1;

这将防止基于名字，您可以使用所有这些列进行检查，我假设匹配的列应该是电子邮件，您可以使用它.

This will prevent based on the first name, you may use all these columns for checking, I assume the matching column should be email, you can use that.

我只是在查询中添加了参数给你一个想法，使用参数绑定来避免sql注入.

或

  select * from customers where .... // 

获取结果集的大小，如果size >0 表示已经有一行了，所以不要插入.

Get the size of result set and if size > 0 that means there is a row already, so do not insert it.

Sql 语句取自 MySQL:如果表中不存在则插入记录 和修改.

Sql statement taken from MySQL: Insert record if not exists in table and modified.

这篇关于如果不存在则插入到 MYSQL 数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！