gpt4 book ai didi

MySQL 在 LOCATE 中使用用户定义的变量时出现非法的排序规则混合

转载 作者:行者123 更新时间:2023-11-29 06:30:39 25 4
gpt4 key购买 nike

我有一个如下所示的查询,用于从表列中删除以特定子字符串开头和结尾的某些子字符串:

UPDATE om_posts SET post_content=REPLACE(post_content, SUBSTRING(
post_content,
LOCATE(' style="', post_content),
LOCATE('"', post_content, LOCATE(' style="', post_content )+ 8) - LOCATE(' style="', post_content ) + 1
),'')
where post_type="post";

我想让它更好地可重用,所以我想抽象出这些字符串。我在mysql中遇到了用户定义的变量并像这样重构:

SET @beginning = ' style="';
SET @ending ='"';

UPDATE om_posts SET post_content=REPLACE(post_content, SUBSTRING(
post_content,
LOCATE(@beginning, post_content),
LOCATE(@ending, post_content, LOCATE(@beginning, post_content )+ 8) - LOCATE(@beginning, post_content ) + 1
),'')
where post_type="post";

但这会产生错误:查询错误 (1267):操作“定位”时使用排序规则 (utf8mb4_unicode_ci,IMPLICIT) 和 (utf8mb4_general_ci,IMPLICIT) 进行非法混合。据我所知,我的语法应该是正确的。我错过了什么?

最佳答案

Every character string literal has a character set and a collation.

For the simple statement SELECT 'string', the string has the connection default character set and collation defined by the character_set_connection and collation_connection system variables.

A character string literal may have an optional character set introducer and COLLATE clause, to designate it as a string that uses a particular character set and collation:

[_charset_name]'string' [COLLATE collation_name]

来自 official documentation

该页面的示例:_utf8mb4'abc' COLLATE utf8mb4_danish_ci

关于MySQL 在 LOCATE 中使用用户定义的变量时出现非法的排序规则混合,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/56450102/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com