How to insert data in mysql (two ways)

MySQL is a very popular relational database that uses SQL (Structured Query Language) to manage and manipulate data. Inserting data in MySQL is very simple but can be confusing if you are a beginner. This article will introduce in detail how to insert data in MySQL and some common considerations.

There are two ways to insert data in MySQL: one is to use the INSERT INTO statement, and the other is to use the LOAD DATA INFILE statement.

1. Using the INSERT INTO statement

The INSERT INTO statement is used to insert data into a MySQL table. The syntax is as follows:

INSERT INTO 表名 (列1, 列2, 列3, ...)
VALUES (值1, 值2, 值3, ...);

Among them, table name is the name of the table you want to insert data into, column 1, column 2, column 3, etc. are the names of the columns in the table, value 1, value 2, value 3, etc. etc. are the values ​​to be inserted. The value must match the column's data type.

For example, if you have a table named customers that contains the id, name, and email columns, you can use the following statement to insert data into the table:

INSERT INTO customers (id, name, email)
VALUES (1, '张三', 'zhangsan@example.com');

You can insert as needed Multiple rows of data. For example:

INSERT INTO customers (id, name, email)
VALUES (2, '李四', 'lisi@example.com'),
       (3, '王五', 'wangwu@example.com'),
       (4, '赵六', 'zhaoliu@example.com');

Note that if you do not want to specify values ​​for all columns, you can use the DEFAULT keyword. For example:

INSERT INTO customers (name, email)
VALUES ('小明', 'xiaoming@example.com');

This will insert a default value in the id column (which may be NULL or another value, depending on the table's definition).

2. Using the LOAD DATA INFILE statement

The LOAD DATA INFILE statement is used to import data from a file into a MySQL table. The syntax is as follows:

LOAD DATA INFILE '文件路径'
INTO TABLE 表名
FIELDS TERMINATED BY ',' (可选)
ENCLOSED BY '"' (可选)
LINES TERMINATED BY '\n';

where the file path is the path and name of the file to be imported, and the table name is the name of the table into which data is to be inserted. FIELDS TERMINATED BY ',' and ENCLOSED BY '"' (optional) specify delimiters between columns in the file and quotes for each field.

For example, if you have a table named customers, It contains the id, name and email columns. You can store the following data in a CSV file called customers_data.csv:

2,'李四','lisi@example.com'
3,'王五','wangwu@example.com'
4,'赵六','zhaoliu@example.com'

You can then load the data into the table using the following statement:

LOAD DATA INFILE 'customers_data.csv'
INTO TABLE customers
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Please check the documentation to learn more about the options and examples of LOAD DATA INFILE.

Some notes

When inserting data in MySQL, please note the following:

• Must have permission to insert data.
• For tables with auto-increment columns, you generally do not need to insert a value for that column. You can insert NULL or not insert the column, and MySQL will automatically generate it for you Increment the value.
• Make sure that the inserted value matches the data type of the column, otherwise the data will not be inserted successfully and an error will be thrown.
• When inserting data into a character column, make sure that Strings are enclosed in quotation marks.
• To avoid inserting duplicate data, you may need to check whether the same data already exists in the table before inserting data.
• In order to improve the performance of inserting data, you can insert Multiple rows of data. This is much faster than inserting a single row of data at a time.

Summary

MySQL is a powerful relational database that makes inserting data very easy. You can use INSERT INTO or LOAD DATA INFILE statement to insert data. Please ensure that the inserted value matches the data type of the column and avoid inserting duplicate data. To improve performance when inserting data, you can insert multiple rows of data.

The above is the detailed content of How to insert data in mysql (two ways). For more information, please follow other related articles on the PHP Chinese website!