MySQL 定义变量后，使用报错

Question

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

Answer 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', '', '');