MySQL - REPLACE 查询



MySQL REPLACE 语句

一般来说,如果我们想向现有表中添加记录,我们会使用 MySQL INSERT 语句。同样,我们也可以使用 MySQL REPLACE 语句添加新记录或替换现有记录。REPLACE 语句类似于 INSERT 语句。

唯一的区别是,使用 INSERT 语句插入记录时,如果现有列具有 UNIQUE 或 PRIMARY KEY 约束,并且新记录为此列具有相同的值,则会生成错误。

对于 REPLACE 语句,如果尝试插入新列,并且该列与具有 UNIQUE 或 PRIMARY KEY 约束的列具有重复值,则旧记录将被新记录完全替换。

语法

以下是 MySQL REPLACE 语句的语法:

REPLACE INTO table_name (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN);

其中,table_name 是需要插入数据的表名,(column1, column2, column3,...columnN) 是列名,(value1, value2, value3,...valueN) 是记录中的值。

示例

让我们从在 MySQL 数据库中创建一个名为 CUSTOMERS 的表开始,该表在 ID 列上具有主键约束,如下所示:

Open Compiler
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );

以下查询将两条记录添加到上面创建的表中:

Open Compiler
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ), (2, 'Khilan', 25, 'Delhi', 1500.00 );

执行以下查询以显示 CUSTOMERS 表中存在的所有记录:

Open Compiler
select * FROM CUSTOMERS;

CUSTOMERS 表中的记录如下:

ID 姓名 (NAME) 年龄 (AGE) 地址 (ADDRESS) 薪水 (SALARY)
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00

现在,让我们尝试插入另一条具有 ID2 的记录:

Open Compiler
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Kaushik', 23, 'Kota', 2000.00 );

由于 ID 列具有主键约束,因此会生成错误,如下所示:

ERROR 1062 (23000): Duplicate entry '2' for key 'customers.PRIMARY'

现在,使用 REPLACE 语句替换表中存在的记录:

Open Compiler
REPLACE INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Kaushik', 20, 'Kota', 2000.00 );

输出

执行上面的查询将产生以下输出:

Query OK, 2 rows affected (0.01 sec)

验证

执行以下 SELECT 语句以验证新记录是否已替换:

Open Compiler
select * from CUSTOMERS;

正如我们在下面的输出中看到的,现有记录已被新记录替换:

ID 姓名 (NAME) 年龄 (AGE) 地址 (ADDRESS) 薪水 (SALARY)
1 Ramesh 32 Ahmedabad 2000.00
2 Kaushik 20 Kota 2000.00

使用 REPLACE 语句插入记录

当使用 REPLACE 语句插入记录时,如果该记录与表中任何现有记录都不匹配,则它将作为新记录添加。

示例

以下查询使用 REPLACE 语句向上面的 CUSTOMERS 表中添加三条新记录:

Open Compiler
REPLACE INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Chaitali', 25, 'Mumbai', 6500.00 ), (4, 'Hardik', 27, 'Bhopal', 8500.00 ), (5, 'Komal', 22, 'Hyderabad', 4500.00 );

输出

执行上面的查询将产生以下输出:

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

验证

执行以下查询以验证上述记录是否已插入到 CUSTOMERS 表中:

Open Compiler
SELECT * FROM CUSTOMERS;

正如我们在下面的 CUSTOMERS 表中看到的,上述记录已作为新记录插入到表中。

ID 姓名 (NAME) 年龄 (AGE) 地址 (ADDRESS) 薪水 (SALARY)
1 Ramesh 32 Ahmedabad 2000.00
2 Kaushik 20 Kota 2000.00
3 Chaitali 25 Mumbai 6500.00
4 Hardik 27 Bhopal 8500.00
5 Komal 22 Hyderabad 4500.00

Learn MySQL in-depth with real-world projects through our MySQL certification course. Enroll and become a certified expert to boost your career.

使用客户端程序替换记录

除了使用 MySQL 查询替换 MySQL 数据库中表的记录外,我们还可以使用客户端程序执行 REPLACE 操作。

语法

以下是各种编程语言中使用 REPLACE 查询的语法:

要通过 PHP 程序从 MySQL 数据库替换表中的记录,我们需要使用 mysqli 函数 query() 执行 REPLACE 语句,如下所示:

$sql="REPLACE INTO TABLE_NAME SET COLUMN_NAME1 = NEW_VALUE, COLUMN_NAME2 = NEW_VALUE..."; $mysqli->query($sql);

要通过 Node.js 程序从 MySQL 数据库替换表中的记录,我们需要使用 mysql2 库的 query() 函数执行 REPLACE 语句,如下所示:

sql="REPLACE INTO table_name (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN)" con.query(sql);

要通过 Java 程序从 MySQL 数据库替换表中的记录,我们需要使用 JDBC 函数 executeUpdate() 执行 REPLACE 语句,如下所示:

String sql="REPLACE INTO TABLE_NAME SET COLUMN_NAME1 = NEW_VALUE, COLUMN_NAME2 = NEW_VALUE..."; statement.executeUpdate(sql);

要通过 Java 程序替换 MySQL 数据库中表中的记录,我们需要使用 MySQL **Connector/Python** 的 **execute()** 函数执行 **ALTER** 语句,如下所示:

replace_query = "REPLACE INTO table_name (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN)" cursorObj.execute(replace_query );

示例

以下是一些程序示例:

$dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'password'; $dbname = 'TUTORIALS'; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if($mysqli->connect_errno ) { printf("Connect failed: %s", $mysqli->connect_error); exit(); } //printf('Connected successfully.'); $sql = "REPLACE INTO tutorials_tbl SET tutorial_id = 1, tutorial_title = 'Java Tutorial', tutorial_author = 'new_author'"; if($result = $mysqli->query($sql)){ printf("Replace statement executed successfully..! "); } $q = "SELECT * FROM tutorials_tbl"; if($res = $mysqli->query($q)){ printf("Records after replace statement are: "); while($row = mysqli_fetch_row($res)){ print_r ($row); } } if($mysqli->error){ printf("Failed..!" , $mysqli->error); } $mysqli->close();

输出

获得的输出如下:

Replace statement executed successfully..!  Records after replace statement are: Array
(
    [0] => 1
    [1] => Java Tutorial
    [2] => new_author
    [3] =>
)
Array
(
    [0] => 2
    [1] => PHP Tut
    [2] => unknown2
    [3] => 2023-08-12
)
var mysql = require('mysql2'); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); //Selecting a Database sql = "USE TUTORIALS" con.query(sql); //Creating a table sql = "CREATE TABLE sales(ID INT UNIQUE, ProductName VARCHAR(255), CustomerName VARCHAR(255), DispatchDate date, DeliveryTime time, Price INT, Location VARCHAR(255));" con.query(sql); //Inserting records into table sql = "INSERT into sales values(1, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada');" con.query(sql); //Displaying records before replacing sql = "Select * from sales" con.query(sql, function (err, result) { if (err) throw err; console.log(result); console.log("************************************************") }); //Replacing the record sql = "REPLACE into sales values(1, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai');" con.query(sql); //Displaying records after replacing sql = "Select * from sales" con.query(sql, function (err, result) { if (err) throw err; console.log(result); }); });

输出

生成的输出如下:

Connected!
[
  {
    ID: 1,
    ProductName: 'Mouse',
    CustomerName: 'Puja',
    DispatchDate: 2019-02-28T18:30:00.000Z,
    DeliveryTime: '10:59:59',
    Price: 3000,
    Location: 'Vijayawada'
  }
]
************************************************
[
  {
    ID: 1,
    ProductName: 'Mobile',
    CustomerName: 'Vanaja',
    DispatchDate: 2019-02-28T18:30:00.000Z,
    DeliveryTime: '10:10:52',
    Price: 9000,
    Location: 'Chennai'
  }
]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class ReplaceQuery { public static void main(String[] args) { String url = "jdbc:mysql://#:3306/TUTORIALS"; String user = "root"; String password = "password"; ResultSet rs; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); //System.out.println("Database connected successfully...!"); String sql = "REPLACE INTO tutorials_tbl SET tutorial_id = 1, tutorial_title = 'Java Tutorial', tutorial_author = 'John Smith'"; st.executeUpdate(sql); System.out.println("Replace query executed successfully..!"); String sql1 = "SELECT * FROM tutorials_tbl"; rs = st.executeQuery(sql1); System.out.println("Table records: "); while(rs.next()) { String tutorial_id = rs.getString("tutorial_id"); String tutorial_title = rs.getString("tutorial_title"); String tutorial_author = rs.getString("tutorial_author"); String submission_date = rs.getString("submission_date"); System.out.println("Id: " + tutorial_id + ", Title: " + tutorial_title + ", Author: " + tutorial_author + ", Submission_date: " + submission_date); } }catch(Exception e) { e.printStackTrace(); } } }

输出

获得的输出如下所示:

Replace query executed successfully..!
Table records: 
Id: 1, Title: Java Tutorial, Author: John Smith, Submission_date: null
Id: 2, Title: Angular Java, Author: Abdul S, Submission_date: 2023-08-08
Id: 3, Title: Learning Java, Author: Sanjay, Submission_date: 2007-05-06
Id: 4, Title: Python Tutorial, Author: Sasha Lee, Submission_date: 2016-09-04
Id: 5, Title: Hadoop Tutorial, Author: Chris Welsh, Submission_date: 2023-08-08
import mysql.connector import datetime #establishing the connection connection = mysql.connector.connect( host='localhost', user='root', password='password', database='tut') #Creating a cursor object cursorObj = connection.cursor() replace_query = "REPLACE INTO tutorials_tbl (tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES (3, 'Learning Java', 'John Doe', '2023-07-28')" cursorObj.execute(replace_query) connection.commit() print("REPLACE query executed successfully.") cursorObj.close() connection.close()

输出

以上代码的输出如下:

REPLACE query executed successfully.
广告