mysql insert on_使用mysql的insert语句插入数据

Summary: in this tutorial, you will learn how to use MySQL INSERT statement to insert data into the database tables.

Simple MySQL INSERT statement

The MySQL INSERT statement allows you to insert data into tables. The following illustrates the syntax of the INSERTstatement:INSERT INTO table(column1,column2...) VALUES (value1,value2,...)

First, after the INSERT INTO, you specify the table name and a list of comma-separated columns inside parentheses.  Then you put a comma-separated values of the corresponding columns inside parentheses followed the VALUESkeyword.

You need to have the INSERT privilege to use the INSERTstatement.

Let’s create a new table named tasksfor practicing the INSERTstatement.CREATE TABLE IF NOT EXISTS tasks (   task_id int(11) NOT NULL 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) )

For example, if you want to insert a new task into the taskstable, you use the INSERT statement as follows:INSERT INTO tasks(subject,start_date,end_date,description) VALUES('Learn MySQL INSERT','2010-01-01','2010-01-02','Start learning..')

After executing the statement, MySQL returns a message to indicate how many rows were affected. In this case, one row were affected.

MySQL INSERT – insert multiple rows

In order to insert multiple rows into a table, you use the INSERTstatement with the following syntax:INSERT INTO table(column1,column2...) VALUES (value1,value2,...), (value1,value2,...), ...

Each row, which you want to insert into the table, is specified by a comma-separated values inside parentheses. For example, to insert multiple tasks in the taskstable, you use the following query:INSERT INTO tasks(subject,start_date,end_date,description) VALUES ('Task 1','2010-01-01','2010-01-02','Description 1'), ('Task 2','2010-01-01','2010-01-02','Description 2'), ('Task 3','2010-01-01','2010-01-02','Description 3');

3 rows affected. Great!

If you specify the values of the corresponding column for all columns in the table, you can ignore the column list in the INSERTstatement as follows:INSERT INTO table VALUES (value1,value2,...)

andINSERT INTO table VALUES (value1,value2,...), (value1,value2,...), ...

Notice that you don’t have to specify the value for auto-increment column e.g., taskidcolumn because MySQL generates its values automatically.

MySQL INSERT with SELECT clause

In MySQL, you can specify the values for the INSERTstatement from a SELECT statement. It is very handy because you can clone a table fully or partially by using the INSERTand SELECTclauses as follows:INSERT INTO table_1 SELECT c1, c2, FROM table_2;

First, we create a new table named tasks_1by cloning the structure of the tasks table as follows:CREATE TABLE tasks_1 LIKE tasks;

Second, we can insert data into the tasks_1 table from the taskstable by using INSERT statement:INSERT INTO tasks_1 SELECT * FROM tasks;

Third, we can check the tasks_1 table to see if we actually clone it from the taskstable.SELECT * FROM tasks_1;

MySQL INSERT  with ON DUPLICATE KEY UPDATE

If the new record that you want to insert may cause duplicate value in PRIMARY KEY or a UNIQUEindex, MySQL will issue an error.  For example, if you execute the following statement:INSERT INTO tasks(task_id,subject,start_date,end_date,description) VALUES (4,'Task 4','2010-01-01','2010-01-02','Description 4');

MySQL will issue an error message:Error Code: 1062. Duplicate entry '4' for key 'PRIMARY' 0.016 sec

However if you specify ON DUPLICATE KEY UPDATE in the INSERTstatement, MySQL will update the old record, for example:INSERT INTO tasks(task_id,subject,start_date,end_date,description) VALUES (4,'Task 4','2010-01-01','2010-01-02','Description 4') ON DUPLICATE KEY UPDATE task_id = task_id + 1;

MySQL issues a message saying that  2 rows affected.

Let’s check the taskstable:SELECT * FROM tasks;

A new record was inserted but the old record with task_idvalue 4 was updated. The above query is equivalent to the following query:UPDATE tasks  SET task_id = task_id+1  WHERE task_id = 1;

In conclusion, when you use   ON DUPLICATE KEY UPDATE in the INSERTstatement with the new record that causes the duplicate value in PRIMARY KEY or UNIQUEindex, MySQL updates the old record and a new record.

In this tutorial, we have shown you how to use various forms of the MySQL INSERT statement to insert data into a database table.

Related Tutorials