循序渐进丨MogDB 数据库中如何调用带变量的SQL脚本

MogDB 2024-07-23

0. 前言

作为一个习惯于使用终端进行运维的DBA，手上肯定需要一批SQL脚本，用于日常的运维和优化工作。这些脚本中，往往需要用户输入一些变量。那么，在 MogDB 数据库中，如何执行脚本？如何给脚本传递变量呢？

1. 执行不带变量的SQL脚本

不带变量的SQL脚本执行相对比较简单。我们分为两种情况讨论。一种是当前输入界面已经在gsql中，去调用SQL脚本；另一种是在SHELL里面，命令行调用gsql并给它传递SQL脚本。

1.1 准备一个测试用的SQL脚本（不含变量）

cat > sess.sql <<EOF
select pid,usename,application_name,xact_start,query_start,waiting,state
from pg_stat_activity
where state = 'active';
EOF

1.2 在gsql里面调用SQL脚本：\i 脚本文件名

gsql提供一个元命令，\i，用于调用SQL脚本。

比如：

gsql -r
\i sess.sql

      pid       | usename | application_name |          xact_start           |          query_start          | waiting | state
----------------+---------+------------------+-------------------------------+-------------------------------+---------+--------
 22872621315840 | tpcc    | gsql             | 2024-07-10 15:40:21.179175+08 | 2024-07-10 15:40:21.179175+08 | f       | active
 22846861993728 | tpcc    | JobScheduler     |                               |                               | f       | active

\i 有几个变种，其中 \ir 是用于SQL脚本中嵌套 \i 时，使用的路径是以被调用的SQL脚本为当前路径的。而 \i+ 和 \ir+ 则是允许脚本进行加密。关于脚本嵌套和脚本加密，这里就不展开了。

1.3 在Shell环境执行gsql + SQL脚本

在Shell环境执行gsql + SQL脚本则有三种用法：

（1）标准用法：-f 脚本文件名

最标准的用法是使用gsql的命令行选项 -f，比如：

gsql -f sess.sql

      pid       | usename | application_name |          xact_start           |          query_start          | waiting | state
----------------+---------+------------------+-------------------------------+-------------------------------+---------+--------
 22873709213440 | tpcc    | gsql             | 2024-07-10 15:49:29.524565+08 | 2024-07-10 15:49:29.524565+08 | f       | active
 22846861993728 | tpcc    | JobScheduler     |                               |                               | f       | active

（2）SHELL输入重定向：< 脚本文件名

另一种用法是把 SQL脚本中的内容模仿标准输入，传给gsql命令，也就是使用输入重定向符号 < + 脚本文件名。比如：

gsql < sess.sql

      pid       | usename | application_name |          xact_start           |          query_start          | waiting | state
----------------+---------+------------------+-------------------------------+-------------------------------+---------+--------
 22873349551872 | tpcc    | gsql             | 2024-07-10 15:56:06.868991+08 | 2024-07-10 15:56:06.868991+08 | f       | active
 22846861993728 | tpcc    | JobScheduler     |                               |                               | f       | active

（3）利用gsql -c 选项：-c “\i 脚本文件名”

这个其实利用gsql里面的\i元命令，其实是绕了一圈，用这种方法应该比较少。比如:

gsql -c "\i sess.sql"

      pid       | usename | application_name |          xact_start           |          query_start          | waiting | state
----------------+---------+------------------+-------------------------------+-------------------------------+---------+--------
 22872307726080 | tpcc    | gsql             | 2024-07-10 16:00:04.077213+08 | 2024-07-10 16:00:04.077213+08 | f       | active
 22846861993728 | tpcc    | JobScheduler     |                               |                               | f       | active

2. 执行带变量的SQL脚本

调用不带变量的SQL脚本还是比较简单的。下面进入困难模式，调用的SQL需要特定的变量。比如，刚才那个示例SQL脚本，输出的是status=‘active’，可是如果希望根据变量来输出不同status的结果，就会相对麻烦很多。

2.1 gsql中的变量使用

幸运的是，gsql的确是支持变量的。内部称为"Variables"。

变量的设置有几种方式，分别是:

gsql命令行选项: -v 变量名称=变量值
gsql内赋值: \set 变量名称变量值
gsql内交互式输入: \prompt 变量名称

而调用的时候，使用 :变量名形式进行调用。但是，这里面有个非常重要的点，:变量名不能放在引号里面，或者说，:变量名不能用引号括起来，因为对于gsql而言，无法判断引号里的冒号是字符串内容的一部分还是变量名的前缀，所以，统一当作字符串内容的一部分来处理。

这个处理逻辑和 Oracle 不一样，Oracle 使用&符号来作为变量的前缀，在sqlplus里set define on（默认选项）时，无论&符号是否被放在引号里面，都会进行替换。

2.2 gsql中的数字类型变量使用

如果需要传入的字符串，这个问题处理起来会很复杂，因此，我们循序渐进，先通过相对简单的传入数字的形式来熟悉变量的使用。

假设现在我们需要检查数据库中空间大小大于x MB的对象。

SQL类似于这样:

cat > relsize.sql <<EOF
select relname,relpages*8192/1024/1024 relsize
from pg_class 
where relpages * 8192/1024/1024 > :size
order by relsize desc;
EOF

2.2.1 gsql命令行选项: -v 变量名称=变量值

从SHELL中调用。

检查大于10MB的：

gsql -v size=10 -f relsize.sql

                 relname                 |  relsize
-----------------------------------------+------------
 snap_global_stat_all_indexes            | 45.5546875
 snap_global_statio_all_indexes          |    41.8125
 snap_class_vital_info                   |      40.25
 snap_global_config_settings             |   35.65625
 snap_global_stat_all_tables             |      33.25
 snap_summary_stat_all_indexes           | 32.4453125
 snap_summary_statio_all_indexes         | 31.7265625
 snap_global_statio_all_tables           | 26.1953125
 snap_summary_stat_all_tables            |   21.34375
 snap_summary_statio_all_tables          | 17.9921875
 snap_summary_transactions_running_xacts | 16.5703125
 snap_global_transactions_running_xacts  | 16.4921875
 snap_global_thread_wait_status          | 16.3828125
 pg_toast_2618                           |    13.6875
 snap_global_os_threads                  | 11.3359375
(15 rows)

检查大于30MB的：

gsql -f relsize.sql -v size=30

             relname             |  relsize
---------------------------------+------------
 snap_global_stat_all_indexes    | 45.5546875
 snap_global_statio_all_indexes  |    41.8125
 snap_class_vital_info           |      40.25
 snap_global_config_settings     |   35.65625
 snap_global_stat_all_tables     |      33.25
 snap_summary_stat_all_indexes   | 32.4453125
 snap_summary_statio_all_indexes | 31.7265625

2.2.2 gsql内元命令: \set 变量名称变量值

从gsql中调用，需先设置变量值。

检查大于10MB的：

gsql 
\set size 10
\i relsize.sql

                 relname                 |  relsize
-----------------------------------------+------------
 snap_global_stat_all_indexes            | 45.5546875
 snap_global_statio_all_indexes          |    41.8125
 snap_class_vital_info                   |      40.25
 snap_global_config_settings             |   35.65625
 snap_global_stat_all_tables             |      33.25
 snap_summary_stat_all_indexes           | 32.4453125
 snap_summary_statio_all_indexes         | 31.7265625
 snap_global_statio_all_tables           | 26.1953125
 snap_summary_stat_all_tables            |   21.34375
 snap_summary_statio_all_tables          | 17.9921875
 snap_summary_transactions_running_xacts | 16.5703125
 snap_global_transactions_running_xacts  | 16.4921875
 snap_global_thread_wait_status          | 16.3828125
 pg_toast_2618                           |    13.6875
 snap_global_os_threads                  | 11.3359375
(15 rows)

30MB同理，这里不再占用篇幅。

2.2.3 gsql内元命令等待输入: \prompt 变量名称

检查大于30MB的：

gsql 
\prompt size
30
\i relsize.sql

             relname             |  relsize
---------------------------------+------------
 snap_global_stat_all_indexes    | 45.5546875
 snap_global_statio_all_indexes  |    41.8125
 snap_class_vital_info           |      40.25
 snap_global_config_settings     |   35.65625
 snap_global_stat_all_tables     |      33.25
 snap_summary_stat_all_indexes   | 32.4453125
 snap_summary_statio_all_indexes | 31.7265625
(7 rows)

10MB同理，不再占用篇幅。

2.2.4 让脚本包含输入提示: \prompt 变量名称

除了以上3种，其实，还可以在SQL脚本中包含输入提示部分。

脚本可以写成(给个新的名字relsize_p.sql)：

cat > relsize_p.sql <<EOF
\echo Please input value of size:
\prompt size
select relname,relpages*8192/1024/1024 relsize
from pg_class 
where relpages * 8192/1024/1024 > :size
order by relsize desc;
EOF

然后可以SHELL调用 gsql：

gsql -f relsize_p.sql
Please input value of size:
40
            relname             |  relsize
--------------------------------+------------
 snap_global_stat_all_indexes   | 45.5546875
 snap_global_statio_all_indexes |    41.8125
 snap_class_vital_info          |      40.25
(3 rows)

或者在gsql中调用该SQL：

gsql
\i relsize_p.sql
MogDB=# \i relsize_p.sql
Please input value of size:
41
            relname             |  relsize
--------------------------------+------------
 snap_global_stat_all_indexes   | 45.5546875
 snap_global_statio_all_indexes |    41.8125
(2 rows)

之所以特意把脚本改了个名字，是因为这是两种不同的思路，relsize.sql是可以SHELL直接后台调用，无需交互的；而relsize_p.sql是强制交互的。如果大家有需要准备常用带变量的脚本，建议准备两套，交互或者不交互。

2.3 gsql中的字符串类型变量使用

字符串类型变量使用会复杂一些。

2.3.1 字符串变量的限制

在gsql的体系里面，变量是无法被引号括起来，下面做个不成功的尝试。

假设，我们需要检查某个数据库参数的值。计划使用类似这样的SQL进行查询：

select name,setting 
from pg_settings
 where name ~ '参数名称'
order by name;

计划使用变量，按照Oracle的习惯，带变量的SQL会写成：

select name,setting 
from pg_settings
 where name ~ ':name'
order by name;

尝试在gsql中跑一下：

gsql -r
\set name index
select name,setting 
from pg_settings
 where name ~ ':name'
order by name;

返回的0行：

 name | setting
------+---------
(0 rows)

显然不符合要求。这就是我前面所说的：变量是无法被引号括起来的。所以，我们只能在给变量的赋值时候，给它配套好引号。

尝试这样写：

gsql -r
\set name 'index'
\echo :name
select name,setting 
from pg_settings
 where name ~ :name
order by name;

失败了：

MogDB=# \set name 'index'
MogDB=# \echo :name
index
MogDB=# select name,setting
MogDB-# from pg_settings
MogDB-#  where name ~ :name
MogDB-# order by name;
ERROR:  column "index" does not exist
LINE 3:  where name ~ index

\set 里的引号被吃掉了，这也好理解，毕竟有时候要传带空格或者其他特殊字符的情况，gsql可以用一对引号来区分变量值的开始与结束位置。

2.3.2 gsql中字符串变量正确定义方法

继续尝试，根据MogDB的规则，单引号里带单引号，需要把用到的单引号写两遍。类似于这样：

\set name '''index'''

也就是前3后3的格局。

测试一下：

\set name '''index'''
\echo :name
select name,setting 
from pg_settings
 where name ~ :name
order by name;

成功了：

MogDB=# \set name '''index'''
MogDB=# \echo :name
'index'
MogDB=# select name,setting
MogDB-# from pg_settings
MogDB-#  where name ~ :name
MogDB-# order by name;
               name               | setting
----------------------------------+---------
 cost_weight_index                | 1
 cpu_index_tuple_cost             | 0.005
 enable_default_index_compression | off
 enable_hypo_index                | off
 enable_index_nestloop            | on
 enable_indexonlyscan             | on
 enable_indexscan                 | on
 enable_indexscan_optimization    | off
 ignore_system_indexes            | off
 max_index_keys                   | 32
(10 rows)

2.3.3 通过\prompt输入时的正确用法

知道了需要用单引号后，\propmt的用法就很好理解了。这里直接前后各一个单引号就好了。因为它用回车符来确定输入终止位置，不需要用单引号来确定起始位置。

\prompt name 
'index'
select name,setting 
from pg_settings
 where name ~ :name
order by name;

成功：

               name               | setting
----------------------------------+---------
 cost_weight_index                | 1
 cpu_index_tuple_cost             | 0.005
 enable_default_index_compression | off
 enable_hypo_index                | off
 enable_index_nestloop            | on
 enable_indexonlyscan             | on
 enable_indexscan                 | on
 enable_indexscan_optimization    | off
 ignore_system_indexes            | off
 max_index_keys                   | 32
(10 rows)

2.3.4 命令行调用 gsql -v的正确用法

命令行调用的时候，需要考虑的是SHELL对单引号的处理。

先准备好SQL脚本：

cat > para.sql <<EOF
select name,setting
from pg_settings
where name ~ :name
order by name;
EOF

SHELL对付单引号，一般有两种做法，一种是反斜杠，一种是双引号。反斜杠在对付没有空格的变量值可以用，如果带上变量值，则会失败。

反斜杠测试。

不带空格的调用：

gsql -v name=\'index\' -f para.sql
               name               | setting
----------------------------------+---------
 cost_weight_index                | 1
 cpu_index_tuple_cost             | 0.005
 enable_default_index_compression | off
 enable_hypo_index                | off
 enable_index_nestloop            | on
 enable_indexonlyscan             | on
 enable_indexscan                 | on
 enable_indexscan_optimization    | off
 ignore_system_indexes            | off
 max_index_keys                   | 32
(10 rows)

没问题！

带空格的测试：

gsql -v name=\'ind ex\' -f para.sql
gsql: FATAL:  database "ex'" does not exist

失败了。SHELL把空格前后当作两个参数来处理。

所以，还是老老实实用双引号稳一点：

gsql -v name="'index'" -f para.sql
               name               | setting
----------------------------------+---------
 cost_weight_index                | 1
 cpu_index_tuple_cost             | 0.005
 enable_default_index_compression | off
 enable_hypo_index                | off
 enable_index_nestloop            | on
 enable_indexonlyscan             | on
 enable_indexscan                 | on
 enable_indexscan_optimization    | off
 ignore_system_indexes            | off
 max_index_keys                   | 32
(10 rows)

3. 总结

gsql调用SQL脚本有三种方法：

gsql输入界面中 \i 文件名
SHELL中 gsql -f 文件名
SHELL中 gsql -c "\i 文件名"

SQL中变量符号为 :变量名
传递变量有三种方法：

gsql输入界面中 \set 变量名变量值
SHELL中 gsql -v 变量名=变量值
SQL中或者gsql中使用 \prmopt 变量名进行交互式输入

字符串值变量使用：

不能在SQL中直接用单引号括起 :变量名
\set var 时变量值前后各三个单引号
\prompt 时前后各一个单引号
gsql -v时变量值里层单引号，外层双引号

是否交互性输入是两种不同的用法，建议使用不同的脚本文件

关于作者

罗海雄，云和恩墨数据库研发架构师，性能优化专家，2012年 ITPUB 全国SQL大赛冠军。他拥有超十年企业级系统设计与优化经验，对SQL优化理解尤其深入，曾服务于甲骨文公司。

END

MogDB 是云和恩墨基于 openGauss 开源内核进行增强提升，推出的一款安稳易用的企业级关系型数据库。其具备金融级高可用和全密态计算的极致安全、面向多核处理器的极致性能、AI自诊断调优的极致智能能力，能够满足从核心交易到复杂计算的企业级业务需求。

访问官网了解更多：www.mogdb.io

产品兼容适配申请：partner@enmotech.com

加微信进入交流群：Roger_database

sql优化数据库脚本 mogdb sql 单引号

文章转载自MogDB，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

循序渐进丨MogDB 数据库中如何调用带变量的SQL脚本

评论