SQL 脚本 - 是否存在 #define 的等价物?

2022-01-04 00:00:00 sql mysql scripting

我有一个脚本，用于构建表和存储过程.例如，我有一列 varchar 类型.varchar 需要一个大小参数，这个大小我也用作存储过程和这些过程中的参数.

I have a script that I use to construct both the tables and stored procedures. For example I have a column of type varchar. varchar requires a size parameter, that size I also use as parameters in stored procedures and within those procedures.

是否有可能具有 #define 的等价物的大小，这样我就可以轻松调整大小而无需更改整个脚本?

is it possible to have thequivalentnt of a #define for its size, so I can easily adjust the size without the necessity of having to change ithroughht the whole of the script?

我正在使用 MySql 工作台.

I am using MySql workbench.

编辑

我试过 SET 和 DECLARE

我有一个脚本 - 这是(删节)

I have a script - this is (abridged)

CREATE TABLE `locations`
(
   `location`  VARCHAR(25)        NOT NULL
);

...
CREATE PROCEDURE AddLocation (IN  location VARCHAR(25)
BEGIN
...
END$$

我想要实现的是用 constant 替换脚本中的值 25 - 类似于创建表的脚本顶部的 #define和存储过程，所以我可以轻松地将 25 更改为另一个数字.

What I am trying to achieve is replace the values 25 in the script with a constant - similar to a #define at the top of the script that creates the table and stored procedures, so I am able to easily change the 25 to another number.

有人找到了解决这个问题的方法吗?

Anybody has found a solution to this problem?

推荐答案

C 预处理器 (cpp) 在历史上与 C 相关联(因此得名)，但它确实是一个可以使用(或滥用)的通用文本处理器) 用于其他用途.

The C Pre Processor (cpp) is historically associated with C (hence the name), but it really is a generic text processor that can be used (or abused) for something else.

考虑这个名为 location.src 的文件(稍后会详细介绍).

Consider this file, named location.src (more on that later).

// C++ style comments works here
/* C style works also */
-- plain old SQL comments also work,
-- but you should avoid using '#' style of comments,
-- this will confuse the C pre-processor ...

#define LOCATION_LEN 25

/* Debug helper macro */
#include "debug.src"

DROP TABLE IF EXISTS test.locations;
CREATE TABLE test.locations
(
   `location` VARCHAR(LOCATION_LEN) NOT NULL
);

DROP PROCEDURE IF EXISTS test.AddLocation;
delimiter $$
CREATE PROCEDURE test.AddLocation (IN location VARCHAR(LOCATION_LEN))
BEGIN
  -- example of macro
  ASSERT(length(location) > 0, "lost or something ?");

  -- do something
  select "Hi there.";
END
$$

delimiter ;

和文件 debug.src，其中包括:

and file debug.src, which is included:

#ifdef HAVE_DEBUG
#define ASSERT(C, T)                                          
  begin                                                       
    if (not (C)) then                                         
      begin                                                   
        declare my_msg varchar(1000);                         
        set my_msg = concat("Assert failed, file:", __FILE__, 
                            ", line: ", __LINE__,             
                            ", condition ", #C,               
                            ", text: ", T);                   
        signal sqlstate "HY000" set message_text = my_msg;    
     end;                                                     
    end if;                                                   
  end
#else
#define ASSERT(C, T) begin end
#endif

编译时:

cpp -E location.src -o location.sql

你得到你正在寻找的代码，用 cpp 扩展 #define 值.

you get the code you are looking for, with cpp expanding #define values.

编译时:

cpp -E -DHAVE_DEBUG location.src -o location.sql

你得到相同的结果，加上 ASSERT 宏(作为奖励发布，以展示可以做什么).

you get the same, plus the ASSERT macro (posted as a bonus, to show what could be done).

假设在测试环境中部署了一个带有 HAVE_DEBUG 的构建(在 5.5 或更高版本中，因为使用了 SIGNAL)，结果如下所示:

Assuming a build with HAVE_DEBUG deployed in a testing environment (in 5.5 or later since SIGNAL is used), the result looks like this:

mysql> call AddLocation("Here");
+-----------+
| Hi there. |
+-----------+
| Hi there. |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call AddLocation("");
ERROR 1644 (HY000): Assert failed, file:location.src, line: 24, condition length(location) > 0, text: lost or something ?

注意文件名、行号和条件如何指向 location.src 中源代码中引发断言的位置，再次感谢 C 预处理器.

Note how the file name, line number, and condition points right at the place in the source code in location.src where the assert is raised, thanks again to the C pre processor.

现在，关于.src"文件扩展名:

Now, about the ".src" file extension:

  • 你可以使用任何东西.
  • 使用不同的文件扩展名有助于生成文件等，并防止混淆.

最初发布为 .xql，为了清晰起见，重命名为 .src.此处与 xml 查询无关.

Originally posted as .xql, renamed to .src for clarity. Nothing related to xml queries here.

与任何工具一样，使用 cpp 可以带来好事，并且以可移植的方式维护 LOCATION_LEN 的用例看起来非常合理.它还可能导致不好的事情，过多的#include、嵌套的#ifdef 地狱、宏等最终会混淆代码，因此您的里程可能会有所不同.

As with any tools, using cpp can lead to good things, and the use case for maintaining LOCATION_LEN in a portable way looks very reasonable. It can also lead to bad things, with too many #include, nested #ifdef hell, macros, etc that at the end obfuscate the code, so your mileage may vary.

有了这个答案，你就明白了(#define、#include、#ifdef、__FILE__、__LINE__、#C、要构建的命令行选项)，所以我希望它应该涵盖所有内容.

With this answer, you get the whole thing (#define, #include, #ifdef, __FILE__, __LINE__, #C, command line options to build), so I hope it should cover it all.

相关文章