Execute MySQL command under shell

In shell development, many times we need to operate MySQL database (for example: query data, export data, etc.), but we can not enter the MySQL command line environment, we need to simulate the environment of MySQL in the shell environment, the use of MySQL-related commands, This paper summarizes several methods of shell operation of MySQL for your reference.

Scenario 1

1. Mysql-uuser-ppasswd-e"Insert logtable values (...)"

Advantages: Simple statement Disadvantages: Supported SQL is relatively simple Scenario 2Prepare an SQL script with the name Update.sql, for example:

2. CREATE TABLE ' user ' (
4. ' ID ' varchar ($) not NULL COMMENT ' primary key ',
6. ' username ' varchar (notNULL COMMENT ' username '),
8. ' Password ' varchar (notNULL COMMENT ' user password '),
10. ' CreateDate ' date not NULL COMMENT ' creation time ',
12. ' Age ' int (one) not NULL COMMENT ' ages ',
14. PRIMARY KEY (' id ')
16. ) Engine=myisam DEFAULT Charset=utf8 comment=' user Information table ';
18. DROP TABLE IF EXISTS ' Visit_log ';
20. CREATE TABLE ' Visit_log ' (
22. ' ID ' varchar (character) set UTF8 not NULL,
24. ' Type ' int (one) is not NULL,
26. ' Content ' text character set UTF8 not NULL,
28. ' CreateDate ' date not NULL,
30. PRIMARY KEY (' id ')
32. ) Engine=myisam DEFAULT charset=latin1 comment=' access log ';

Create a new update_mysql.sh with the following content: [Python]View Plaincopy

1. Use CHBDB;
2. SOURCE Update.sql

Then execute the following command: [Python]View Plaincopy

1. Cat update_mysql.sh | MySQL--user=root-ppassword

Advantages: Support for complex SQL scripts Disadvantages:1> requires two files: Update.sql and update_mysql.sh2> Once there is an error in the middle, the script will not execute, for example: if the first table already exists, the following exception is reported: Error 1050 (42S01) at line 1 in File: ' Update.sql ': Table ' user ' already exists then the script exits and the second table cannot be created. Scenario 3Create a new shell script with the following format:  

1. #!/bin/bash
2. mysql-u*-h*-p* <<eof
3. Your SQL script.
4. Eof

For example:  

2. #!/bin/bash
4. Mysql-uroot-ppassword <<eof
6. Use CHBDB;
8. CREATE TABLE User (
10. ID varchar ($) not NULL COMMENT ' primary key ',
12. Username varchar () not NULL COMMENT ' user name ',
14. Password varchar () not NULL COMMENT ' user password ',
16. CreateDate date not NULL COMMENT ' creation time ',
18. Age int (one) not NULL COMMENT ' ages ',
20. PRIMARY KEY (' id ')
22. ) Engine=myisam DEFAULT Charset=utf8 comment=' user Information table ';

Advantages:1> support for complex SQL scripts 2> no additional files required Disadvantages:1> table name, field cannot use single quotation marks, need to modify the original SQL statement 2> if there is an error in the middle, then the script will not execute, for example: if the first table already exists, the following exception will be reported: Error 1050 (42S01) at line 1 in file: ' Update.sql ': Table ' user ' already exists then the script exits and the second table cannot be created. Scenario 4Prepare an SQL script, such as Update.sql, and then execute the following command: [Python]View Plaincopy

1. Mysql-uroot-ppassword < Update.sql

Pros: Support for complex SQL scripts Disadvantages:1> Once there is an error in the middle, the script will not execute, for example: if the first table already exists, the following exception will be reported: Error 1050 (42S01) at line 1 in file: ' Update.sql ': Table ' user ' already exi STS then the script exits, and the second table cannot be created.

Execute MySQL command under shell