Mysql5.7变为GreatSQL 8.0.32-25过程中，SQL语句报错及解决方案

原创已于 2025-05-29 19:50:44 修改 · 1.2k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#1024程序员节 #数据库

于 2024-10-24 09:59:52 首次发布

数据库专栏收录该内容

10 篇文章

订阅专栏

考虑兼容国产化数据库，现需要将Mysql5.7变为GreatSQL，在执行部分sql时，发现在Mysql5.7无报错，在GreatSQL有报错，在此记录一下遇到的几个错误。

1.ERROR 1231 (NO_AUTO_CREATE_USER)

1.1.报错提示

ERROR 1231 (42000) at line 16494: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

1.2.报错部分

主要是最后一句

/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb3 */ ;
/*!50003 SET character_set_results = utf8mb3 */ ;
/*!50003 SET collation_connection  = utf8mb3_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;

1.3.报错原因

5.7时不会存在这个问题，但是如果是8.0版本MySQL，sql_mode 中不再支持 NO_AUTO_CREATE_USER。

1.3.1.补充

NO_AUTO_CREATE_USER 是一个已废弃的 SQL 模式选项，在早期的 MySQL 版本中用于控制 GRANT
语句的行为。当这个模式被激活时，它会阻止 GRANT 语句自动创建不存在的用户账户，除非在授予权限的同时也指定了用户的密码。这意味着，如果你尝试为一个尚不存在的用户授予权限，而且没有提供密码，MySQL 不会自动创建这个用户，而是会失败或者忽略这个请求。
例如，如果你执行如下 GRANT 语句：
GRANT SELECT ON database.* TO ‘newuser’@‘localhost’;
在 NO_AUTO_CREATE_USER 模式下，如果 ‘newuser’@‘localhost’ 这个用户之前不存在，并且没有同时提供一个密码，这个命令将会失败，以防止无意识地创建用户账户。然而，从 MySQL 8.0.11 开始，包括 NO_AUTO_CREATE_USER 在内的几个与账户管理相关的旧特性已被移除，因此在 MySQL 8.0 及以后的版本中，你不再需要也不应该使用 NO_AUTO_CREATE_USER 这个模式选项。在这些新版本中，如果想要创建用户并授予权限，必须明确地使用 CREATE USER 语句创建用户，然后再使用 GRANT 语句分配权限，并且在创建用户时提供密码。

1.4.解决方案

删掉NO_AUTO_CREATE_USER模式，（主要是最后一句报错）修改后如下

/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb3 */ ;
/*!50003 SET character_set_results = utf8mb3 */ ;
/*!50003 SET collation_connection  = utf8mb3_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;

2.ERROR 1064 (IF NOT EXISTS)

2.1.报错提示

1064 - 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 'IF NOT EXISTS `count` int(10) NOT NULL AFTER `number`' at line 1

2.2.报错部分

ALTER TABLE test_table_name ADD COLUMN IF NOT EXISTS `count` int(10) NOT NULL AFTER `number`;

2.3.报错原因

在 GreatSQL 中，ALTER TABLE 语句通常不支持 IF NOT EXISTS 选项。

2.4.解决方案

2.4.1.移除IF NOT EXISTS

ALTER TABLE test_table_name ADD COLUMN `count` int(10) NOT NULL AFTER `number`;

确保不会重复执行可直接去除IF NOT EXISTS，否则重复执行会报错。

2.4.2.使用存储过程先判断再添加

添加字段存储过程（对于已经存在的字段不再添加）

DROP PROCEDURE IF EXISTS p_alter_table;
delimiter //
CREATE PROCEDURE p_alter_table()
begin
IF NOT EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='test' AND table_name='your_table_name' AND COLUMN_NAME='your_column') 
   and EXISTS(SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND table_name='your_table_name') THEN
   	 -- 你要执行的语句	
     ALTER TABLE your_table_name ADD COLUMN your_column int(10) NOT NULL AFTER `number`;
END IF;
end;
//
delimiter ;
call p_alter_table();

3.ERROR 1101 - (default value)

3.1.报错提示

1101 - BLOB, TEXT, GEOMETRY or JSON column 'describe' can't have a default value

3.2.报错部分

CREATE TABLE IF NOT EXISTS `test_table` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
	`time` int(10) NOT NULL,
	`describe` MEDIUMTEXT DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

3.3.报错原因

在 GreatSQL 中，BLOB、TEXT、MEDIUMTEXT、LONGTEXT、GEOMETRY 或 JSON 等类型的列不允许设置默认值。它们的存储方式和使用场景与其他基本数据类型不同。设置默认值可能会引发复杂性和性能问题。

3.4.解决方案

3.4.1.移除默认值

CREATE TABLE IF NOT EXISTS `test_table` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
	`time` int(10) NOT NULL,
	`describe` MEDIUMTEXT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

3.4.2.使用其他数据类型

如果需要一个可以有默认值的列，可以考虑使用 VARCHAR 或其他支持默认值的类型。

CREATE TABLE IF NOT EXISTS `test_table` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
	`time` int(10) NOT NULL,
	`describe` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

3.4.3.应用程序层处理默认值

在插入数据时，应用程序可以在没有提供该字段的值时手动设置默认值。

4.ERROR 1071 - (Specified key was too long)

4.1.报错提示

1071 - Specified key was too long; max key length is 1000 bytes

4.2.报错部分

CREATE TABLE if NOT EXISTS `test_max` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT ,
  `name` int(10) NOT NULL ,
  `log` varchar(2550) NOT NULL ,
  `update_time` int(11) NOT NULL ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `log` (`log`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

4.3.报错原因

索引键的长度超过了数据库的限制

4.3.1补充

MySQL 中的索引长度限制与字符集和存储引擎的关系如下 3 个字节，而 utf8mb4 每个字符最多占用 4 个字节。因此，如果使用 utf8mb4 字符集，VARCHAR(1000) 最大长度将是 4000 字节，这可能超出索引的限制。
存储引擎： InnoDB 和 MyISAM：不同的存储引擎对索引的限制有所不同。InnoDB 的索引键长度限制为 767 字节（在某些配置下可以调整），而 MyISAM 则是 1000 字节。
配置：对于 InnoDB，如果启用了 innodb_large_prefix 选项，可以将索引长度提高到 3072 字节（在 DYNAMIC 和 COMPRESSED 行格式下）。

4.4.解决方案

4.4.1.减小索引字段的长度

CREATE TABLE if NOT EXISTS `test_max` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT ,
  `name` int(10) NOT NULL ,
  `log` varchar(50) NOT NULL ,
  `update_time` int(11) NOT NULL ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `log` (`log`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

4.4.2.使用前缀索引

CREATE TABLE if NOT EXISTS `test_max` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT ,
  `name` int(10) NOT NULL ,
  `log` varchar(50) NOT NULL ,
  `update_time` int(11) NOT NULL ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `log` (`log`(50)) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

5.ERROR 1178 - (Engine doesn’t support)

5.1.报错提示

1178 - The storage engine for the table doesn't support native partitioning

5.2.报错部分

CREATE TABLE `test_table` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `start_time` int(10) unsigned NOT NULL COMMENT '开始时间',
  `end_time` int(10) unsigned NOT NULL COMMENT '结束时间',
  PRIMARY KEY (`id`,`end_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE (end_time)
(PARTITION p0 VALUES LESS THAN MAXVALUE ENGINE = MyISAM);

5.3.报错原因

国产化数据库不支持数据库引擎为MyISAM的表进行分区操作。

5.4.解决方案

5.4.1.修改表引擎为InnoDB

CREATE TABLE `test_table` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `start_time` int(10) unsigned NOT NULL COMMENT '开始时间',
  `end_time` int(10) unsigned NOT NULL COMMENT '结束时间',
  PRIMARY KEY (`id`,`end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (end_time)
(PARTITION p0 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

6.ERROR 1055 - (this is incompatible with sql_mode=only_full_group_by)

6.1.报错提示

1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a.STATUS' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

6.2.报错部分

在 SELECT 语句中选择了一个名为 STATUS 的列（即 a.STATUS）。
但是，STATUS 没有出现在 GROUP BY 子句中，也没有使用任何聚合函数。
因此，MySQL 无法确定如何处理这个列，导致错误。

6.3.报错原因

这是由于数据库sql_mode默认值包含only_full_group_by，在此模式下，如果SELECT查询中的列没有出现在GROUP BY子句中，MySQL会抛出错误ERROR 1055 (42000)。这意味着如果查询中包含聚合函数（如SUM, COUNT等），那么除了这些函数外所有的SELECT列都必须在GROUP BY子句中声明。

6.4.解决方案

6.4.1.修改配置文件

修改 MySQL 配置文件（通常是 my.cnf 或 my.ini），找到 sql_mode 选项并去掉 ONLY_FULL_GROUP_BY，然后重启 MySQL 服务。

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL

7.ERROR 1418 - (This function has none of DETERMINISTIC)

7.1.报错提示

1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

7.2.报错部分

DROP FUNCTION IF EXISTS `TEST_ARRAY`;
delimiter ;;
CREATE FUNCTION `TEST_ARRAY`(setA VARCHAR(255), setB VARCHAR(255))
RETURNS int(11)
BEGIN
    DECLARE idx INT DEFAULT 1;
    DECLARE tmpStr VARCHAR(255);

    -- 遍历setB中的每个元素
    WHILE idx <= LENGTH(setB) - LENGTH(REPLACE(setB, ',', '')) + 1 DO
        -- 获取setB中的当前元素
        SET tmpStr = SUBSTRING_INDEX(SUBSTRING_INDEX(setB, ',', idx), ',', -1);
        
        -- 检查当前元素是否在setA中
        IF FIND_IN_SET(tmpStr, setA) > 0 THEN
            RETURN 1; -- 如果存在交集，则返回1
        END IF;
        
        SET idx = idx + 1;
    END WHILE;
    
    RETURN 0; -- 如果不存在交集，则返回0
END
;;
delimiter ;

7.3.报错原因

MySQL 在启用了二进制日志（log_bin）的情况下，对于函数的声明有更严格的要求。如果创建的存储函数访问了数据表或执行了 SQL 操作，MySQL 要求明确声明函数的特性。

7.4.报错分析

DETERMINISTIC：表示该函数是确定性的，即对于相同的输入，它总是返回相同的结果。声明为DETERMINISTIC 可以减少MySQL 在二进制日志中记录的复杂性。
NO SQL：表示函数不执行任何 SQL 语句。适用于纯计算的函数。
READS SQL DATA：表示函数会读取数据库数据，但不会修改数据。通常在查询时使用。
MODIFIES SQL DATA：表示函数会修改数据库数据，适用于进行插入、更新或删除等操作的函数。

7.5.解决方案

7.5.1.明确声明函数的特性

DROP FUNCTION IF EXISTS `TEST_ARRAY`;
delimiter ;;
CREATE FUNCTION `TEST_ARRAY`(setA VARCHAR(255), setB VARCHAR(255))
RETURNS int(11)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE idx INT DEFAULT 1;
    DECLARE tmpStr VARCHAR(255);

    -- 遍历setB中的每个元素
    WHILE idx <= LENGTH(setB) - LENGTH(REPLACE(setB, ',', '')) + 1 DO
        -- 获取setB中的当前元素
        SET tmpStr = SUBSTRING_INDEX(SUBSTRING_INDEX(setB, ',', idx), ',', -1);
        
        -- 检查当前元素是否在setA中
        IF FIND_IN_SET(tmpStr, setA) > 0 THEN
            RETURN 1; -- 如果存在交集，则返回1
        END IF;
        
        SET idx = idx + 1;
    END WHILE;
    
    RETURN 0; -- 如果不存在交集，则返回0
END
;;
delimiter ;

8.ERROR 1146 - Table ‘mysql.global_priv’ doesn’t exist

8.1.报错提示

1146 - Table 'mysql.global_priv' doesn't exist

8.2.报错部分

SELECT User, Host, JSON_EXTRACT(Priv, '$.password_lifetime') AS password_lifetime, JSON_EXTRACT(Priv, '$.password_last_changed') AS password_last_changed FROM mysql.global_priv ;

8.3.报错原因

从 MySQL 8.0 版本开始，mysql.global_priv 表被移除。MySQL 8.0 引入了新的权限系统和表结构，权限管理已不再依赖 global_priv 表。

8.4.解决方案

8.4.1.查询 mysql.user 表，查看所有用户的密码过期时间等信息

SELECT user, host, password_expired, password_last_changed, password_lifetime FROM mysql.user;