Shell Operation MySQL

#!/bin/bash
#mysql. Sh
Mysql= ' Mysql-uroot-ptogest '
Sql= ' show databases;use togest_sy_department;show tables; '
$mysql-E "$sql"

Mysql-uroot-ptogest--tee= "Mysql.log"

Shell Operation MySQL
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

[Python] View plaincopy

1. MYSQL-UUSER-PPASSWD-E "Insert logtable values (...)"

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

[Python] View plaincopy

1. CREATE TABLE user (
2. ID varchar ($) Not NULL COMMENT ' primary key ',
3. Username varchar () NOT NULL COMMENT ' user name ',
4. password varchar (NOT null COMMENT ' user password '),
5. createdate date not null COMMENT ' creation time ',
6. age int (one) NOT null COMMENT ' ages ' ,
7. PRIMARY KEY ( ID )
8. ) Engine=myisam DEFAULT charset=utf8 comment= ' user Information table ';
9. DROP TABLE IF EXISTS visit_log ;
10. CREATE TABLE visit_log (
11. ID varchar (character) set UTF8 not NULL,
12. Code>type Int (one) not NULL,
13. content text character set UTF8 not null,
14. Crea Tedate date not NULL,
15. PRIMARY KEY ( ID )

16. ) Engine=myisam DEFAULT charset=latin1 Comment= ' Access log ';

    Create a new update_mysql.sh with the following contents:

[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

Pros: Support for complex SQL scripts
Disadvantages:
1> need two files: Update.sql and update_mysql.sh
2> 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 3
Create a new shell script with the following format:

[Python] View plaincopy

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

For example:

[Python] View plaincopy

1. #!/bin/bash
2. Mysql-uroot-ppassword <<eof
3. Use CHBDB;
4. CREATE TABLE User (
5. ID varchar ($) Not NULL COMMENT ' primary key ',
6. Username varchar () not NULL COMMENT ' user name ',
7. Password varchar () not NULL COMMENT ' user password ',
8. CreateDate date not NULL COMMENT ' creation time ',
9. Age int (one) not NULL COMMENT ' ages ',
10. PRIMARY KEY ( id )
11. ) 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> 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 4
Prepare 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 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.

大家知道在mysql命令行中使用source命令，即使中间出错，后续脚本也会继续执行，但是如上几种方式，均无法解决该问题，如果大家有好的建议，请回复，谢谢。

For more articles, please follow: http://www.ilovehai.com

Shell Operation MySQL