MySQL写存储过程时报错

1267- Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=

问题描述 ：

SELECT distinct a.user_id INTO userID FROM authorization_record a WHERE a.open_id = openId AND a.auth_type = '5001'

错误如下：

[Err]1267- Illegal mixofcollations (utf8_unicode_ci,IMPLICIT)and(utf8_general_ci,IMPLICIT)foroperation'='

解决方法 ：

将比较等式一边进行字符串转换，如改为“ CONVERT(b.fullCode USING utf8) COLLATE utf8_unicode_ci ”

SELECT distinct a.user_id INTO userID FROM authorization_record a WHERE a.open_id = CONVERT(openId USING utf8) COLLATE utf8_unicode_ci AND a.auth_type = '5001'

彻底解决phpcms

v9升级后,文章发布出现：MySQL

Query

:

SELECT

*

FROM

`withli_a`.`v9_keyword`

WHERE

`keyword`

=

'吼吼'

AND

`siteid`

=

'1'

LIMIT

1

MySQL

Error

:

Illegal

mix

of

collations

(latin1_swedish_ci,IMPLICIT)

and

(gbk_chinese_ci,COERCIBLE)

for

operation

'='

MySQL

Errno

:

1267

Message

:

Illegal

mix

of

collations

(latin1_swedish_ci,IMPLICIT)

and

(gbk_chinese_ci,COERCIBLE)

for

operation

'='

（上面这个错误代码是phpcms

v9中出现的）

出现上面的错误，是数据库字符集不一致的问题。

解决办法，进phpmyadmin里面执行SQL语句：

修改表的编码：

ALTER

TABLE

`v9_keyword`

DEFAULT

CHARACTER

SET

gbk

COLLATE

gbk_chinese_ci

修改字段的编码：

ALTER

TABLE

`v9_keyword

`

CHANGE

`keyword

`

`keyword

`

CHAR(

100

)

CHARACTER

SET

gbk

COLLATE

gbk_chinese_ci

NOT

NULL

然后就可以了.

---