Execute mysql command _ MySQL in linux shell

In linux, shell executes mysql commands. in shell development, we often need to operate mysql databases (such as querying data and exporting data), but we cannot enter 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(...)"

Advantage: simple statement
Disadvantage: the supported SQL statements are relatively simple.

Solution 2
Prepare an SQL script named update. SQL, for example:

Create table 'user' ('id' varchar (36) not null comment 'primary key', 'username' varchar (50) not null comment 'username ', 'password' varchar (50) not null comment 'user password', 'createdate' date not null comment' creation time', 'age' int (11) not null comment 'age', primary key ('id') ENGINE = MyISAM default charset = utf8 COMMENT = 'User information'; drop table if exists 'Visit _ log '; create table 'Visit _ log' ('id' varchar (36) character set utf8 not null, 'type' int (11) not null, 'content' text character set utf8 not null, 'createdate' date not null, primary key ('id') ENGINE = MyISAM default charset = latin1 COMMENT = 'Access log ';

Create an update_mysql.sh with the following content:

    use chbdb;      source update.sql

Run the following command:

  cat update_mysql.sh | mysql --user=root -ppassword

Advantage: supports complex SQL scripts
Disadvantages:
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 exists

Then the script exits and the second table cannot be created.
Solution 3
Create a shell script in the following format:

#! /Bin/bash mysql-u *-h *-p * <
  
  
For example:
 
  
#! /Bin/bash mysql-uroot-ppassword <
    
    
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:
 
    
    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 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.