MySQL 定义变量后，使用报错
MySQL 定义变量后，使用报错
SET @REDISHOST = '172.16.2.129';
INSERT INTO `config_info` VALUES (2, 'ruoyi-gateway-dev.yml', 'DEFAULT_GROUP', 'spring:\n redis:\n ** host: '@REDISHOST'\n** port: 6379\n password: \n cloud:\n gateway:\n discovery:\n locator:\n lowerCaseServiceId: true\n enabled: true\n routes:\n # 认证中心\n - id: ruoyi-auth\n uri: lb://ruoyi-auth\n predicates:\n - Path=/auth/**\n filters:\n # 验证码处理\n - CacheRequestFilter\n - ValidateCodeFilter\n - StripPrefix=1\n # 代码生成\n - id: ruoyi-gen\n uri: lb://ruoyi-gen\n predicates:\n - Path=/code/**\n filters:\n - StripPrefix=1\n # 定时任务\n - id: ruoyi-job\n uri: lb://ruoyi-job\n predicates:\n - Path=/schedule/**\n filters:\n - StripPrefix=1\n # 系统模块\n - id: ruoyi-system\n uri: lb://ruoyi-system\n predicates:\n - Path=/system/**\n filters:\n - StripPrefix=1\n # 数据采集模块\n - id: ruoyi-demo\n uri: lb://ruoyi-demo\n predicates:\n - Path=/demo/**\n filters:\n - StripPrefix=1\n # 数据中心模块\n - id: ruoyi-datacenter\n uri: lb://ruoyi-datacenter\n predicates:\n - Path=/datacenter/**\n filters:\n - StripPrefix=1\n # 文件服务\n - id: ruoyi-file\n uri: lb://ruoyi-file\n predicates:\n - Path=/file/**\n filters:\n - StripPrefix=1\n\n# 安全配置\nsecurity:\n # 验证码\n captcha:\n enabled: true\n type: math\n # 防止XSS攻击\n xss:\n enabled: true\n excludeUrls:\n - /system/notice\n # 不校验白名单\n ignore:\n whites:\n - /auth/logout\n - /auth/login\n - /auth/register\n - /*/v2/api-docs\n - /csrf\n', 'bd28870e95ea58b70b80d6a2fe4e6bc8', '2020-05-14 14:17:55', '2022-11-22 07:42:40', 'nacos', '172.16.88.242', '', '', '网关模块', 'null', 'null', 'yaml', '', '');
报错提示：
[ERR] 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 '@REDISHOST'\n port: 6379\n password: \n cloud:\n gateway:\n disco' at line 1
在MySQL中使用变量时，需要使用CONCAT函数将变量与字符串拼接在一起，你可以尝试修改你的SQL语句，将@REDISHOST变量拼接到字符串中，如下所示：
SET @REDISHOST = '172.16.2.129';
INSERT INTO `config_info` VALUES (2, 'ruoyi-gateway-dev.yml', 'DEFAULT_GROUP', CONCAT('spring:\n redis:\n host: ', @REDISHOST, '\n port: 6379\n password: \n cloud:\n gateway:\n discovery:\n locator:\n lowerCaseServiceId: true\n enabled: true\n routes:\n # 认证中心\n - id: ruoyi-auth\n uri: lb://ruoyi-auth\n predicates:\n - Path=/auth/**\n filters:\n # 验证码处理\n - CacheRequestFilter\n - ValidateCodeFilter\n - StripPrefix=1\n # 代码生成\n - id: ruoyi-gen\n uri: lb://ruoyi-gen\n predicates:\n - Path=/code/**\n filters:\n - StripPrefix=1\n # 定时任务\n - id: ruoyi-job\n uri: lb://ruoyi-job\n predicates:\n - Path=/schedule/**\n filters:\n - StripPrefix=1\n # 系统模块\n - id: ruoyi-system\n uri: lb://ruoyi-system\n predicates:\n - Path=/system/**\n filters:\n - StripPrefix=1\n # 数据采集模块\n - id: ruoyi-demo\n uri: lb://ruoyi-demo\n predicates:\n - Path=/demo/**\n filters:\n - StripPrefix=1\n # 数据中心模块\n - id: ruoyi-datacenter\n uri: lb://ruoyi-datacenter\n predicates:\n - Path=/datacenter/**\n filters:\n - StripPrefix=1\n # 文件服务\n - id: ruoyi-file\n uri: lb://ruoyi-file\n predicates:\n - Path=/file/**\n filters:\n - StripPrefix=1\n\n# 安全配置\nsecurity:\n # 验证码\n captcha:\n enabled: true\n type: math\n # 防止XSS攻击\n xss:\n enabled: true\n excludeUrls:\n - /system/notice\n # 不校验白名单\n ignore:\n whites:\n - /auth/logout\n - /auth/login\n - /auth/register\n - /*/v2/api-docs\n - /csrf\n'), 'bd28870e95ea58b70b80d6a2fe4e6bc8', '2020-05-14 14:17:55', '2022-11-22 07:42:40', 'nacos', '172.16.88.242', '', '', '网关模块', 'null', 'null', 'yaml', '', '');
MySQL 报错提示中显示语法错误，提示变量 @REDISHOST 在 INSERT INTO 语句中的引用存在问题。具体地，在 spring 中 Redis 的 host 属性应该使用字符串字面量代替变量名。
您可以尝试将 INSERT INTO 语句修改为以下形式：
INSERT INTO `config_info` VALUES (
2,
'ruoyi-gateway-dev.yml',
'DEFAULT_GROUP',
'spring:
redis:
host: \'' + @REDISHOST + '\'
port: 6379
password:
cloud:
gateway:
discovery:
locator:
lowerCaseServiceId: true
enabled: true
routes:
# 认证中心
- id: ruoyi-auth
uri: lb://ruoyi-auth
predicates:
- Path=/auth/**
filters:
# 验证码处理
- CacheRequestFilter
- ValidateCodeFilter
- StripPrefix=1
# 代码生成
- id: ruoyi-gen
uri: lb://ruoyi-gen
predicates:
- Path=/code/**
filters:
- StripPrefix=1
# 定时任务
- id: ruoyi-job
uri: lb://ruoyi-job
predicates:
- Path=/schedule/**
filters:
- StripPrefix=1
# 系统模块
- id: ruoyi-system
uri: lb://ruoyi-system
predicates:
- Path=/system/**
filters:
- StripPrefix=1
# 数据采集模块
- id: ruoyi-demo
uri: lb://ruoyi-demo
predicates:
- Path=/demo/**
filters:
- StripPrefix=1
# 数据中心模块
- id: ruoyi-datacenter
uri: lb://ruoyi-datacenter
predicates:
- Path=/datacenter/**
filters:
- StripPrefix=1
# 文件服务
- id: ruoyi-file
uri: lb://ruoyi-file
predicates:
- Path=/file/**
filters:
- StripPrefix=1
# 安全配置
security:
# 验证码
captcha:
enabled: true
type: math
# 防止XSS攻击
xss:
enabled: true
excludeUrls:
- /system/notice
# 不校验白名单
ignore:
whites:
- /auth/logout
- /auth/login
- /auth/register
- /*/v2/api-docs
- /csrf
',
'bd28870e95ea58b70b80d6a2fe4e6bc8',
'2020-05-14 14:17:55',
'2022-11-22 07:42:40',
'nacos',
'172.16.88.242',
'',
'',
'网关模块',
'null',
'null',
'yaml',
'',
''
);
这里使用了字符串拼接来将 @REDISHOST 的值插入到 SQL 语句中的字符串字面量中。
报错信息是你的sql有问题， @REDISHOST'\n port: 6379\n password: \n cloud:\n gateway:\n disco 靠近这个语句的地方，引号应该是不匹配的，看下是sql太长还是什么问题
多了单引号，把@REDISHOST前后的单引号都去掉试下：
SET @REDISHOST = '172.16.2.129';
INSERT INTO `config_info` VALUES (2, 'ruoyi-gateway-dev.yml', 'DEFAULT_GROUP', 'spring:\n redis:\n ** host: @REDISHOST\n** port: 6379\n password: \n cloud:\n gateway:\n discovery:\n locator:\n lowerCaseServiceId: true\n enabled: true\n routes:\n # 认证中心\n - id: ruoyi-auth\n uri: lb://ruoyi-auth\n predicates:\n - Path=/auth/**\n filters:\n # 验证码处理\n - CacheRequestFilter\n - ValidateCodeFilter\n - StripPrefix=1\n # 代码生成\n - id: ruoyi-gen\n uri: lb://ruoyi-gen\n predicates:\n - Path=/code/**\n filters:\n - StripPrefix=1\n # 定时任务\n - id: ruoyi-job\n uri: lb://ruoyi-job\n predicates:\n - Path=/schedule/**\n filters:\n - StripPrefix=1\n # 系统模块\n - id: ruoyi-system\n uri: lb://ruoyi-system\n predicates:\n - Path=/system/**\n filters:\n - StripPrefix=1\n # 数据采集模块\n - id: ruoyi-demo\n uri: lb://ruoyi-demo\n predicates:\n - Path=/demo/**\n filters:\n - StripPrefix=1\n # 数据中心模块\n - id: ruoyi-datacenter\n uri: lb://ruoyi-datacenter\n predicates:\n - Path=/datacenter/**\n filters:\n - StripPrefix=1\n # 文件服务\n - id: ruoyi-file\n uri: lb://ruoyi-file\n predicates:\n - Path=/file/**\n filters:\n - StripPrefix=1\n\n# 安全配置\nsecurity:\n # 验证码\n captcha:\n enabled: true\n type: math\n # 防止XSS攻击\n xss:\n enabled: true\n excludeUrls:\n - /system/notice\n # 不校验白名单\n ignore:\n whites:\n - /auth/logout\n - /auth/login\n - /auth/register\n - /*/v2/api-docs\n - /csrf\n', 'bd28870e95ea58b70b80d6a2fe4e6bc8', '2020-05-14 14:17:55', '2022-11-22 07:42:40', 'nacos', '172.16.88.242', '', '', '网关模块', 'null', 'null', 'yaml', '', '');
- 可以查看手册：MySQL 语言结构 用户定义的变量 中的内容