Execute MySQL COMMANDS IN SHELL

Original article link

In Shell Development, we often need to operate MySQL databases (such as querying data and exporting data), but we cannot access the MySQL command line environment, it is necessary to simulate the MySQL environment in the shell environment and use MySQL-related commands. This article summarizes several methods for using shell to operate MySQL for your reference.

Solution 1

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

 

Advantages : Simple statement Disadvantages : The supported SQL statements are relatively simple. Solution 2 Prepare an SQL script named update. SQL, for example:

2. Create Table 'user '(
4. 'Id' varchar (36) Not null comment'Primary key',
6. 'Username' varchar (50) Not null comment'Username',
8. 'Password' varchar (50) Not null comment'User password',
10. 'Createdate' date not null comment'Creation time',
12. 'Age' int (11) Not null comment'Age',
14. Primary Key ('id ')
16. ) Engine = MyISAM default charset = utf8 comment ='User info table';
18. Drop table if exists 'visit _ log ';
20. Create Table 'visit _ log '(
22. 'Id' varchar (36) Character Set utf8 not null,
24. 'Type' int (11) 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 an update_mysql.sh with the following content:[Python]View plaincopy

1. Use chbdb;
2. Source update. SQL

  Run the following command:[Python]View plaincopy

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

 Advantages: Supports complex SQL scriptsDisadvantages:1> two files are required: update. SQL and update_mysql.sh.2> once an error occurs in the middle, the script will not be executed. 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 existsThen the script exits and the second table cannot be created.Solution 3Create a shell script in 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 (36) Not null comment'Primary key',
12. Username varchar (50) Not null comment'Username',
14. Password varchar (50) Not null comment'User password',
16. Createdate date not null comment'Creation time',
18. Age int (11) Not null comment'Age',
20. Primary Key ('id ')
22. ) Engine = MyISAM default charset = utf8 comment ='User info table';

 

Advantages: 1> support for complex SQL scripts 2> no additional files Disadvantages: 1> table names and fields cannot use single quotes. You must modify the original SQL statement. 2> once an error occurs in the middle, the script will not be executed. 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. Solution 4 Prepare an SQL script, such as update. SQL, and then execute the following command: [Python] View plaincopy

1. Mysql-uroot-ppassword <update. SQL

advantages: supports complex SQL scripts disadvantages: 1> once an error occurs in the middle, the script is not executed. 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 and the script exits. The second table cannot be created. As you know, if you use the source command in the MySQL command line, the script will continue to be executed even if an error occurs in the middle. However, none of the above methods can solve the problem, if you have any suggestions, please reply. Thank you.