Mysql Data Import Export Method and Tool Description (1- MYSLQIMPORT UTILITY) MYSQLIMPORT Text File Import Tool Introduction
Translation statement: This article comes from Sam's TEACH YOURSELF MYSQL in 21 Days Book part of the book The unclear place is increasing; if there is a translation or incorrect place, please correct it.
Translator: David Euler, SCU. De_euler-david@yahoo.com.cn Time: 2004/04/24 Yuchuan University
1). Mysqlimport's grammatical introduction: MySQLIMPORT is a very efficient tool for the mysql load (or import) data in a mysql / bin directory. This is a command line tool. There are two parameters and a large number of options available. This tool imports a text file into the database and table you specify. For example, we want to import data into the table custermers from the file customers.txt: mysqlimport meet_a_geek customer.txt Note: Here CUSTOMERS.TXT is the text file we want to import data, and Meet_a_geek is the database we have to operate The table name in the database is Customers. The data format of the text file here must be consistent with the record format in the Customers table, otherwise the mysqlimport command will be wrong. The name of the table is the first end file string of the imported file, another example: mysqlimport meet_a_geek cus.to.me.txt, then we will import the contents in the file into the CUS table in the database meet_a_geek . In the above example, only two parameters are used, and more options are not used, and the options for MySQLIMPORT are described below.
2). MYSQLIMPORT: Options Features - DEL - DELETE New Data Import Data Table All Information in Deleting Data Tables - F or --force Regardless Whether you encounter an error, mysqlimport will force to insert data -i or --ignore mysqlimport skips or ignores those rows with the same unique keyword, and the data in the import file will be ignored. -l or -lock-tables data is inserted before locking the table, which prevents the user's query and update when updating the database. -r or-replace This option is reversed to the role of the -i option; this option will replace the same unique key record in the table. --fields-enclosed- by = CHAR specifies what is included in the record of data in the text file, and the data is enclosed in double quotes. By default, the data is not caced by the character. --fields-terminated- by = char specifies the separator between the values of each data, in the file separated by the file, the separator is the period. You can use this option to specify a separator between the data. The default separator is a jumper (tab) - line-terminated- by = STR This option specifies the separation string or character of the line between the line between the line between the line and the line. By default, mysqlimport is separated by newline. You can choose to replace a single character with a string: a new line or a carriage return. The options commonly used by mysqlimport commands include the -v display version (Versice), the -p prompt input password (Password). 3). Example: Import a record format in the line of files in a comma separator, "1", "ORD89876", "1 Dozen Roses", "19991226" Our task is to put this file Data Import to Table ORDERS in Database Meet_A_geek, we use this command: bin / mysqlimport -prl -fields-enclosed-by = "-fields-tERMINATED-by =, meet_a_geek orders.txt This command may look very uncomfortable, but When you are familiar with it, this is very simple. The first part, bin / mysqlimport, tell the operating system you want to run, the mysqlimport under the mysql / bin directory, option P is required to enter a password, so you ask you to change the database Before entering the password, it will be safer. We use the R option because we want to replace data in the file in the form of the only keyword with the file record in the table. Our form Not the latest, you need to update the data in the file, thus use R this option, replacing the records already in the database. L Options are locked in the table when we insert data, this will stop users from us. The table is queried or changed when the table is updated.
