Shell to operate MySQL

Shell to operate MySQL

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 Mysql-uuser-ppasswd-e "insert logtable values (...)"  Advantages: Simple statement Disadvantages: The supported SQL statements are relatively simple. Solution 2Prepare an SQL script named update. SQL, for example: Create Table 'user' (<br/> 'id' varchar (36) not null comment 'Primary key', <br/> 'username' varchar (50) not null comment 'username', <br/> 'Password' varchar (50) not null comment 'user password ', <br/> 'createdate' date not null comment 'creation time', <br/> 'age' int (11) not null comment 'age ', <br/> Primary Key ('id') <br/>) engine = MyISAM default charset = utf8 comment = 'user information '; <br/> drop table if exists 'visit _ log'; <br/> Create Table 'visit _ log' (<br/> 'id' varchar (36) character Set utf8 not null, <br/> 'type' int (11) not null, <br/> 'content' text character set utf8 not null, <br/> 'createdate' date not null, <br/> Primary Key ('id') <br/>) engine = MyISAM default charset = Latin1 comment = 'Access log ';Create an update_mysql.sh with the following content: Use chbdb; <br/> source update. SQLRun the following command: Cat update_mysql.sh | MySQL -- user = root-ppassword  Advantages: Supports complex SQL scripts Disadvantages:1> two files are required: update. once an error occurs between SQL and update_mysql.sh2>, the script will not be executed. 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 and the script exits. The second table cannot be created. Solution 3Create a shell script in the following format: #! /Bin/bash <br/> mysql-u *-H *-p * <EOF <br/> your SQL script. <br/> EOFFor example: #! /Bin/bash <br/> mysql-uroot-ppassword <EOF <br/> Use chbdb; <br/> Create Table user (<br/> ID varchar (36) not null comment 'Primary key', <br/> username varchar (50) not null comment 'username', <br/> password varchar (50) not null comment 'user password ', <br/> createdate date not null comment 'creation time', <br/> Age int (11) not null comment 'age ', <br/> Primary Key ('id') <br/>) engine = MyISAM default charset = utf8 comment = 'user information ';  Advantages:1> support for complex SQL scripts 2> no additional files Disadvantages:1> the table name and field cannot use single quotes. You need to 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 error 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. Solution 4Prepare an SQL script, such as update. SQL, and then execute the following command: Mysql-uroot-ppassword <update. SQL  Advantage: supports complex SQL scripts Disadvantages:1> once an error occurs in the middle, the script will not be executed. For example, if the first table already exists, the following error will be 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. We all know that using the source command in the MySQL command line will continue to execute the script even if an error occurs in the middle. However, none of the above methods can solve this problem. If you have good suggestions, please reply. Thank you.