当前位置：首页 > article >正文

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

article 2024/10/27 12:43:01

考虑兼容国产化数据库，现需要将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;