MySQL命令行执行SQL脚本文件 原创 数据库 2022年2月25日 14:06 夏至未至 1561 当前内容 6119 字，在路上，马上到，马上到 ### 目录 [TOC] ### 何为sql文件 记录着需要执行的sql语句，通常以 `.sql` 为后缀的文件，例如： [root@localhost /]# cat code.sql # 测试创建数据库 create database code1; # 使用这个数据库 use code1; # 数据库下创建表 create table code1_tb1 (a int , b int); [root@localhost /]# code.sql 即为sql脚本文件，内部可以写多条sql语句，然后批量执行，那么重点，这种sql文件执行的方式有哪些，我们实践总结下。 ### 执行方式分类 #### 会话执行sql文件 通过连接数据库，在会话中使用 source 命令来导入sql文件，以实现批量执行。 ##### 语法 mysql> source /code.sql; ##### 验证 souce 后跟sql文件绝对路径，实际验证： [root@localhost /]# mysql -uroot -p123456 -S /var/lib/mysql/mysql.sock mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.7.37 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.02 sec) mysql> mysql> source /code.sql; Query OK, 1 row affected (0.02 sec) Database changed Query OK, 0 rows affected (0.98 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | code1 | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use code1; Database changed mysql> mysql> show tables; +-----------------+ | Tables_in_code1 | +-----------------+ | code1_tb1 | +-----------------+ 1 row in set (0.00 sec) mysql> #### 非会话执行sql文件 这种方式，不需要连接数据库，不需要在会话窗口执行 ##### 语法 mysql -uroot -p123456 -S /var/lib/mysql/mysql.sock < /code.sql ##### 验证 测试数据： [root@localhost /]# cat code.sql # 测试创建数据库 create database code2; # 使用这个数据库 use code2; # 数据库下创建表 create table code2_tb2 (a int , b int); [root@localhost /]# 验证如下： [root@localhost /]# mysql -uroot -p123456 -S /var/lib/mysql/mysql.sock < /code.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost /]# [root@localhost /]# [root@localhost /]# mysql -uroot -p123456 -S /var/lib/mysql/mysql.sock mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 5.7.37 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | code1 | | code2 | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) mysql> mysql> use code2; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-----------------+ | Tables_in_code2 | +-----------------+ | code2_tb2 | +-----------------+ 1 row in set (0.05 sec) mysql> ### 附加非会话执行sql语句 mysql 还有一种非会话执行sql语句能力(非sql文件)，靠一个选项 `-e`，`mysql --help` 能查到 `-e` 选项如下： [root@localhost ~]# mysql --help ... -e, --execute=name Execute command and quit. (Disables --force and history file.) ... [root@localhost ~]# #### 语法 mysql -h -P -u -p -e"sql语句" 例如： [root@localhost /]# mysql -uroot -p123456 -S /var/lib/mysql/mysql.sock -e"create database code3;use code3;create table code3_tb3 (a int, b int);" mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost /]# #### 验证 [root@localhost /]# mysql -uroot -p123456 -S /var/lib/mysql/mysql.sock mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 20 Server version: 5.7.37 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | code1 | | code2 | | code3 | | mysql | | performance_schema | | sys | +--------------------+ 7 rows in set (0.03 sec) mysql> use code3; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-----------------+ | Tables_in_code3 | +-----------------+ | code3_tb3 | +-----------------+ 1 row in set (0.00 sec) mysql> 如此，执行多个sql语句，也能达命令行批量执行sql语句的效果，只不过，其不是使用sql文件。 本文标题： MySQL命令行执行SQL脚本文件 本文作者： 夏至未至 发布时间： 2022年2月25日 14:06 最近更新： 2022年2月28日 10:31 原文链接： 许可协议： 署名-非商业性-禁止演绎 4.0 国际(CC BY-NC-ND 4.0) 请按协议转载并保留原文链接及作者 MySQL(28) 上一个 Linux单库多表串行造MySQL数据脚本 下一个 您已输入用户名，客户机操作系统将保留此用户名 当前文章评论暂未开放,请移步至留言处留言。
