What is the return value of mysql insert?

In mysql, the return value of the insert statement is the ID of the new data. The INSERT statement is used to insert one or more rows of tuple data into an existing table in the database. The syntax is "INSERT INTO table(column1,column2...) VALUES (value1,value2,...);"; "INSERT INTO" The parentheses after the clause specify the table name and a comma-separated list of columns.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

The return value of insert is the ID of the new data. Of course, the premise is that the database supports auto-incrementing ID primary keys; if no auto-incrementing primary key is defined, a special number will be returned.

MySQL INSERT: Insert data (add data)

After the database and table are successfully created, data needs to be inserted into the database table. In MySQL, you can use the INSERT statement to insert one or more rows of tuple data into an existing table in the database.

1. Simple MySQL INSERT statement

MySQL INSERT statement is used to insert one or more rows into a table middle. The syntax of the INSERT statement is illustrated below:

INSERT INTO table(column1,column2...)
VALUES (value1,value2,...);

First , after the INSERT INTO clause, specify the table name within parentheses and separate it with commas List of columns.
Then , place the comma-separated values ​​of the corresponding columns in parentheses after the VALUES keyword.

Before executing the insert statement, you need to have the INSERT permission to execute the INSERT statement.

Let us create a new table named tasks to practice the INSERT statement, refer to the following create statement-

USE testdb;

CREATE TABLE IF NOT EXISTS tasks (
    task_id INT(11) AUTO_INCREMENT,
    subject VARCHAR(45) DEFAULT NULL,
    start_date DATE DEFAULT NULL,
    end_date DATE DEFAULT NULL,
    description VARCHAR(200) DEFAULT NULL,
    PRIMARY KEY (task_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

For example, if you want to To insert a task into the tasts table, use the INSERT statement as follows:

INSERT INTO tasks(subject,start_date,end_date,description)
VALUES('Learn MySQL INSERT','2017-07-21','2017-07-22','Start learning..');

After executing this statement, MySQL returns a message to notify the number of affected rows. In this case, one row is affected.

Now use the following statement to query the data in tasks, as shown below-

SELECT * FROM tasks;

Execute the above query statement and get the following results-

+---------+--------------------+------------+------------+------------------+
| task_id | subject            | start_date | end_date   | description      |
+---------+--------------------+------------+------------+------------------+
|       1 | Learn MySQL INSERT | 2017-07-21 | 2017-07-22 | Start learning.. |
+---------+--------------------+------------+------------+------------------+
1 row in set

2. MySQL INSERT - Insert multiple rows

If you want to insert multiple rows into the table at one time, you can use the INSERT statement with the following syntax:

INSERT INTO table(column1,column2...)
VALUES (value1,value2,...),
       (value1,value2,...),
...;

In this form, the list of values ​​for each row is separated by commas. For example, to insert multiple rows into the tasks table, use the following statement:

INSERT INTO tasks(subject,start_date,end_date,description)
VALUES ('任务-1','2017-01-01','2017-01-02','Description 1'),
       ('任务-2','2017-01-01','2017-01-02','Description 2'),
       ('任务-3','2017-01-01','2017-01-02','Description 3');

After executing the above statement, return -

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Now query tasks The data in the table is as follows-

select * from tasks;

Execute the above query statement and get the following results-

+---------+--------------------+------------+------------+------------------+
| task_id | subject            | start_date | end_date   | description      |
+---------+--------------------+------------+------------+------------------+
|       1 | Learn MySQL INSERT | 2017-07-21 | 2017-07-22 | Start learning.. |
|       2 | 任务-1             | 2017-01-01 | 2017-01-02 | Description 1    |
|       3 | 任务-2             | 2017-01-01 | 2017-01-02 | Description 2    |
|       4 | 任务-3             | 2017-01-01 | 2017-01-02 | Description 3    |
+---------+--------------------+------------+------------+------------------+
4 rows in set

If you specify the values ​​of the corresponding columns for all columns in the table, you can Ignore the column list in the INSERT statement like this:

INSERT INTO table
VALUES (value1,value2,...);

or -

INSERT INTO table
VALUES (value1,value2,...),
       (value1,value2,...),
...;

Note that it is not necessary to auto-increment the column (e.g. taskid column) specify the value because MySQL automatically generates values ​​for auto-incrementing columns.

3. MySQL INSERT with SELECT clause

In MySQL, you can use the columns and values ​​returned by the SELECT statement to Populate the value of the INSERT statement. This feature is very convenient because you can copy the table completely or partially using the INSERT and SELECT clauses like this:

INSERT INTO table_1
SELECT c1, c2, FROM table_2;

Suppose you want to copy the tasks The table is copied to the tasks_bak table.

First , create a new table named tasks_bak by copying the structure of the tasks table, as shown below:

CREATE TABLE tasks_bak LIKE tasks;

The second step, use the following INSERT statement to insert the data in the tasks table into the tasks_bak table:

INSERT INTO tasks_bak
SELECT * FROM tasks;

The third step, check the data in the tasks_bak table to see if the copy from the tasks table is actually completed.

mysql> select * from tasks;
+---------+--------------------+------------+------------+------------------+
| task_id | subject            | start_date | end_date   | description      |
+---------+--------------------+------------+------------+------------------+
|       1 | Learn MySQL INSERT | 2017-07-21 | 2017-07-22 | Start learning.. |
|       2 | 任务-1             | 2017-01-01 | 2017-01-02 | Description 1    |
|       3 | 任务-2             | 2017-01-01 | 2017-01-02 | Description 2    |
|       4 | 任务-3             | 2017-01-01 | 2017-01-02 | Description 3    |
+---------+--------------------+------------+------------+------------------+
4 rows in set

4. MySQL INSERT and ON DUPLICATE KEY UPDATE

If the new row violates the primary key (PRIMARY KEY) or UNIQUEConstraint, MySQL will cause an error. For example, if you execute the following statement:

INSERT INTO tasks(task_id,subject,start_date,end_date,description)
VALUES (4,'Test ON DUPLICATE KEY UPDATE','2017-01-01','2017-01-02','Next Priority');

MySQL will be unhappy and throw you an error message:

Error Code: 1062. Duplicate entry '4' for key 'PRIMARY' 0.016 sec

Because the primary key task_id column in the table already exists There is a row with a value of 4, so the statement violates the PRIMARY KEY constraint.

However, if you specify the ON DUPLICATE KEY UPDATE option in the INSERT statement, MySQL will insert a new row or update the original row record with a new value.

For example, the following statement updates the row where task_id is 4 with the new task_id and subject.

INSERT INTO tasks(task_id,subject,start_date,end_date,description)
VALUES (4,'Test ON DUPLICATE KEY UPDATE','2017-01-01','2017-01-02','Next Priority')
ON DUPLICATE KEY UPDATE 
   task_id = task_id + 1, 
   subject = 'Test ON DUPLICATE KEY UPDATE';

After executing the above statement, MySQL sends a message saying that row 2 is affected. Now, let's take a look at the data in the tasks table:

mysql> select * from tasks;
+---------+------------------------------+------------+------------+------------------+
| task_id | subject                      | start_date | end_date   | description      |
+---------+------------------------------+------------+------------+------------------+
|       1 | Learn MySQL INSERT           | 2017-07-21 | 2017-07-22 | Start learning.. |
|       2 | 任务-1                       | 2017-01-01 | 2017-01-02 | Description 1    |
|       3 | 任务-2                       | 2017-01-01 | 2017-01-02 | Description 2    |
|       5 | Test ON DUPLICATE KEY UPDATE | 2017-01-01 | 2017-01-02 | Description 3    |
+---------+------------------------------+------------+------------+------------------+
4 rows in set

The new row was not inserted, but the task_id value was updated to 4 OK. The above INSERT ON DUPLICATE KEY UPDATE statement is equivalent to the following UPDATE statement:

UPDATE tasks 
SET 
    task_id = task_id + 1,
    subject = 'Test ON DUPLICATE KEY UPDATE'
WHERE
    task_id = 4;

【相关推荐：mysql视频教程】

The above is the detailed content of What is the return value of mysql insert?. For more information, please follow other related articles on the PHP Chinese website!