MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Data Back Up / Import Data / Question No: 176

How to use mysqlimport client?

Can you explain, how to use mysqlimport client to import data into MySQL table?

Answer No: 176

The mysqlimport client is used to import data into MySQL table.  It is really just a command-line version of the LOAD DATA INFILE SQL statement. Its general syntax follows:

mysqlimport [options] database textfile1 [textfile2 ... textfileN]

Useful options of mysqlimport client

  • --columns, -c: This option should be used when the number or ordering of the fields in the target file does not match that found in the table. For example, suppose you were inserting the following target file, which orders the fields as client_id, order_id, sub_total, shipping_cost, total_cost, and order_time:
    45633,12309,22.04,5.67,27.71,2005-12-19 01:13:42
    942,12310,11.50,3.40,14.90,2005-12-19 01:15:12
    7879,12311,95.99,15.00,110.99,2005-12-19 01:15:22
  • Suppose your table lists the fields in this order: order_id, client_id, order_time, sub_total, shipping_cost, and total_cost. You can re-arrange the input fields during the parsing process so that the data is inserted in the proper location, by including this option:
    --columns=client_id,order_id,sub_total,shipping_cost,total_cost,and order_time
  • --compress, -C: Including this option compresses the data flowing between the client and the server, assuming that both support compression. This option is most effective if you’re loading a target file that does not reside on the same server as the database.
  • --debug, -#: This option is used to create trace files when debugging.
  • --delete, -d: This option deletes the target table’s contents before importing the targetfile’s data.
  • --fields-terminated-by=, --fields-enclosed-by=, --fields-optionally-enclosed-by=,--fields-escaped-by=: These four options determine mysqlimport’s behavior in terms of how both fields and lines are recognized during the parsing procedure.
  • --force, -f: Including this option causes mysqlimport to continue execution even if errors occur during execution.
  • --help, -?: Including this option generates a short help file and a comprehensive list of the options discussed in this section.
  • --host, -h: This option specifies the server location of the target database. The default is localhost
  • --ignore, -i: This option causes mysqlimport to ignore any rows located in the target file that share the same primary or unique key as a row already located in the table.
  • --ignore-lines=n: This option tells mysqlimport to ignore the first n lines of the targetfile. It's useful when the target file contains header information that should bedisregarded.
  • --lines-terminated-by=: This option determines how mysqlimport will recognize each separate line in the file.
  • --lock-tables, -l: This option write-locks all tables located in the target database for the duration of mysqlimport’s execution.
  • --local, -L: This option specifies that the target file is located on the client. By default, it is assumed that this file is located on the database server; therefore, you need to includethis option if you’re executing this command remotely and have not uploaded the file to the server.
  • --low-priority: This option delays execution of mysqlimport until no other clients are reading from the table.
  • --password=your_password, -pyour_password: This option is used to specify the password component of your authentication credentials. If the your_password part of this option is omitted, you will be prompted for the password.
  • --port, -P: If the target MySQL server is running on a nonstandard port (MySQL’s standard port is 3306), you need to specify that port value with this option.
  • --replace, -r: This option causes mysqlimport to overwrite any rows located in the targetfile that share the same primary or unique key as a row already located in the table.
  • --silent, -s: This option tells mysqlimport to output only error information.
  • --socket, -S: This option should be included if a nondefault socket file had been declared when the MySQL server was started.
  • --ssl: This option specifies that SSL should be used for the connection.
  • --user, -u: By default, mysqlimport compares the name/host combination of the executing system user to the mysql privilege tables, ensuring that the executing user possesses adequate permissions to carry out the requested operation. Because it’s often useful toperform such procedures under the guise of another user, you can specify the “user”component of credentials with this option.
  • --verbose, -v: This option causes mysqlimport to output a host of potentially useful information pertinent to its behavior.
  • --version, -V: This option causes mysqlimport to output version information and exit.

Related MySQL FAQs to the Above FAQ

How-to-use-LOAD-DATA-INFILE-statement How to use LOAD DATA INFILE statement?

How-to-import-data-from-Microsoft-Excel-file-into-table-with-LOAD-DATA-INFILE-statement How to import data from Microsoft Excel file into table with LOAD DATA INFILE statement?

How-to-import-data-from-text-file-into-table-with-LOAD-DATA-INFILE-statement How to import data from text file into table with LOAD DATA INFILE statement?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.