返回顶部

OutOfMemory.CN技术专栏-> MySQL-> 使用mysql的insert语句插入数据

使用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 INSERT statement:

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 VALUES keyword.

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

Let’s create a new table named tasks for practicing the INSERT statement.

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 tasks table, 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 INSERT statement 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 tasks table, 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 INSERT statement as follows:

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

and

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

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

MySQL INSERT with SELECT clause

In MySQL, you can specify the values for the INSERT statement from a SELECT statement. It is very handy because you can clone a table fully or partially by using the INSERT and SELECT clauses as follows:

INSERT INTO table_1
SELECT c1, c2, FROM table_2;

First, we create a new table named tasks_1 by 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 tasks table 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 tasks table.

SELECT * FROM tasks_1;

mysql insert into select from example

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 UNIQUE index, 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 INSERT statement, 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 tasks table:

SELECT * FROM tasks;

mysql insert on duplicate key update

A new record was inserted but the old record with task_id value 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 INSERT statement with the new record that causes the duplicate value in PRIMARY KEY or UNIQUE index, 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.

推荐阅读:
支持

0

反对

0

发表评论