在MySQL和MariaDB中创建交叉兼容的垃圾字符串函数时出错

Question

正如您所知道的，小型版本的MariaDB (5.5-)应该与MySQL兼容(我已经发现了缺陷，但他们说它的90%)。我在我们的环境中都使用MySQL，在AWS上使用MySQL，在本地Dev服务器和Dev盒上使用Maria。我目前正试图破解一个函数，该函数将自动生成带有随机数据的行。

表的结构将非常简单，如下所示

DROP DATABASE IF EXISTS `foodb`;
CREATE DATABASE `foodb`;
USE `foodb`;

DROP TABLE IF EXISTS `footable`;
CREATE TABLE `footable` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `garbage`    varchar(128) DEFAULT garbageString(),
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

其中的函数garbageString()应该是这样的

DROP FUNCTION IF EXISTS garbageString;
CREATE FUNCTION garbageString($length int)
  RETURNS varchar(128)
  BEGIN

    SET @chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    SET @charLen = length(@chars);

    SET @randomString = '';

    WHILE length(@randomString) < $length DO
      SET @randomString = concat(@randomString, substring(@chars,CEILING(RAND() * @charLen),1));
    END WHILE;

    RETURN @randomString ;
  END;

我很头疼，但是在MySQL和Maria中，我都想让这个函数运行，这里有一个错误转储

MySQL [foodatabase]> CREATE FUNCTION garbageString($length int)
    ->   RETURNS varchar(128)
    ->   BEGIN
    -> 
    ->     SET @chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5
MySQL [foodatabase]>     SET @charLen = length(@chars);
Query OK, 0 rows affected (0.03 sec)

MySQL [foodatabase]> 
MySQL [foodatabase]>     SET @randomString = '';
Query OK, 0 rows affected (0.03 sec)

MySQL [foodatabase]> 
MySQL [foodatabase]>     WHILE length(@randomString) < $length DO
    ->       SET @randomString = concat(@randomString, substring(@chars,CEILING(RAND() * @charLen),1));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHILE length(@randomString) < $length DO
      SET @randomString = concat(@rando' at line 1
MySQL [foodatabase]>     END WHILE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END WHILE' at line 1
MySQL [foodatabase]> 
MySQL [foodatabase]>     RETURN @randomString ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURN @randomString' at line 1
MySQL [foodatabase]>   END;

这是玛丽亚送的

MariaDB [foodatabase]> CREATE FUNCTION garbageString($length int)
    ->   RETURNS varchar(128)
    ->   BEGIN
    -> 
    ->     SET @chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 5
MariaDB [foodatabase]>     SET @charLen = length(@chars);
Query OK, 0 rows affected (0.00 sec)

MariaDB [foodatabase]> 
MariaDB [foodatabase]>     SET @randomString = '';
Query OK, 0 rows affected (0.00 sec)

MariaDB [foodatabase]> 
MariaDB [foodatabase]>     WHILE length(@randomString) < $length DO
    ->       SET @randomString = concat(@randomString, substring(@chars,CEILING(RAND() * @charLen),1));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHILE length(@randomString) < $length DO
      SET @randomString = concat(@rando' at line 1
MariaDB [foodatabase]>     END WHILE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'END WHILE' at line 1
MariaDB [foodatabase]> 
MariaDB [foodatabase]>     RETURN @randomString ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RETURN @randomString' at line 1
MariaDB [foodatabase]>   END;

有什么方法可以让这个函数正常工作，并让它同时使用这两种DB类型呢？

看起来他们都在同一地区窒息，我只是不想要任何意想不到的事情

Answer 1

当使用cmdline客户端定义函数或过程时，需要更改DELIMITER。命令行客户端检查当前查询分隔符的输入，此时拆分多个查询，然后逐个向服务器发送查询。

通常的分隔符是;，但在ANSI过程语言中也使用它作为命令分隔符，所以您的过程代码被分解成一些本身不是有效的SQL语句的小和平号。因此，您需要将客户端分隔符更改为其他内容，例如//

DELIMITER //

在此之后，您可以在没有当前错误的情况下很好地输入过程文本，然后添加//，告诉客户端将完整的过程文本作为一条语句发送到服务器。

然后，如果需要，可以将分隔符更改为;：

DELIMITER ;