How to parse data input into a MySQL database table

Introduction: MySQL databaseThe application scope is relatively broad, and there is still a lot of knowledge involved in MySQL database operations. When you have created a database and a table, the first thing you think of is to input data into the database table. this involves how to add data to the database. next we will discuss this issue:

1. The general method we use is the insert Statement (assuming that your version is not very low). She has the following forms:

Insert into tablename values (value1, value2 ,...)

Insert into tablename (fieldname1, fieldname2,...) values (value1, value2 ,...)

Insert into tablename set fieldname1 = value1, fieldname2 = value2 ,...

Insert into tablename (fieldname1, fieldname2) select fieldname1, fieldname2 from tablename1

The above basically lists how to insert data with insert. Of course, you can also use insert statements to insert several rows into a table at the same time, as shown below:

Insert into tablename values (...),(...),...

2. In the mysql runtime environment, we can also use the load data statement to read the DATA in the file into the table.Generally, this file is directly read by the server on the host. therefore, you must have the file permission and the file must be fully readable. if your version is new enough, you can provide local information. As you read the file from the client and transmit the content to the server, you do not need the file permission.

If you do not provide a local address, the server locates it as follows:

1) if your filename is an absolute path, the server will start searching for the file from the root directory.

2) If your filename is a relative path, the server will start searching for the file from the database data directory.

If you provide local, the file will be located as follows:

1) if your filename is an absolute path, the client will start searching for the file from the root directory.

2) If your filename is a relative path, the client will start searching for the file from the current directory.

After talking about it for a long time, maybe you still don't understand the specific format. Let's look at it first:

Load data [LOCAL] INFILE 'filename' into table tablename import_options [(fieldname_list)]

The import options syntax is:

Fields

Terminated by 'Char'

Enclosed by 'Char'

Escaped by 'Char'

Lines

Terminated by 'string'

Below we will explain it:

1) fields terminated by char specifies the characters of the columns to be separated. The missing time is assumed that the column values are separated by tabs.

2) fields enclosed by char indicates that the column value should be included in the specified characters. quotation marks are usually used. The missing time is used. It is assumed that the column value is not included in any character.

3) fields escaped by char indicates the escape character used to escape special characters. Missing time indicates no escape character

4) lindes escaped by string specifies the string to end the input line (which can be multiple characters). The line break is assumed to terminate the line break.

Note the use of escape characters. for example, \ 'indicates single quotation marks, \ "indicates double quotation marks, and so on. there are also some options not mentioned here, such as ignore n lines, which can control the first n rows of the file not to be read into the database. for more information, see mysql Chinese reference.

After talking about the above, let's take a look at a specific line of commands:

Load data [local] infile "sample.txt" into table sample fields terminated by "," enclosed "\""

3. In the system command line, you can use the mysqlimport utility to read the content of the text file to an existing table (In fact, mysqlimport is used to implement the load data command line interface). After calling mysqlimport, she generates a load data statement. The syntax is similar to load data:

Mysqlimport [options] sample_db filename

Similarly, let's take a look at the command:

Mysqlimport Cu root [-- local] Cfields-terminated-by = ","-fields-enclosed-by = "\" "samp_db samp_table.txt

(REMARK: handle)

I have summarized so much knowledge about how to input data into the MySQL database table. If you have summed up a better method, please share it with you.