1、mysql 1267错误
ERROR:Illegal mix of collations (latin1_swedish_ci,IMPLICI
ERROR:Illegal mix of collations (latin1_swedish_ci,IMPLICI
T) and (utf8_general_ci,COERCIBLE) for operation 'like'
最后的like是出错的语法,
1)检查:数据库的编码集,
show variables like "%character%"; show variables like "%collation%";
--
collation_server latin1_swedish_ci
collation_database utf8_general_ci
collation_connection utf8_general_ci
执行后看看三种mysql的编码集
2)针对查询结果比较,server的编码集可以忽略,后两种需要保持一致,我使用的是utf-8所以目前是正确的
SET collation_connection=utf8_general_ci
3)如果还不行,那就是个别表或个别表的字段编码集错误,修改就可以
4)在my.ini中可以改变server的编码集,但一般不用。
附:mysql也给了sql语句中编码集的转变
http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html
2、log_bin_trust_function_creators错误
现象:
当有mysql本地或远程建立function或procedure时报上面的错误
经试验是log_bin_trust_function_creators值为off导致
设置:set global log_bin_trust_function_creators=1;但重启后失效
永久windows下my.ini[mysqld]區段加上log_bin_trust_function_creators=1
linux下/etc/my.cnf下my.ini[mysqld]區段加上log_bin_trust_function_creators=1
很奇怪,我在linux下装过不会有这种问题啊!记一下。
3、mysql终端窗口function与procedure的建立
通常见到“;”系统会认为sql命令行结束,此时语法是
1. delimiter // --》注意//前有空格 把分隔符改成双杠,这样到中间 FROM T的时候mysql不会认为SQL命令已经结束了;
2. CREATE PROCEDURE p()
3. BEGIN
4. SELECT * FROM T;
5. END;//
6.
7. delimiter ;--》把分隔符再改回来
最后的like是出错的语法,
1)检查:数据库的编码集,
show variables like "%character%"; show variables like "%collation%";
--
collation_server latin1_swedish_ci
collation_database utf8_general_ci
collation_connection utf8_general_ci
执行后看看三种mysql的编码集
2)针对查询结果比较,server的编码集可以忽略,后两种需要保持一致,我使用的是utf-8所以目前是正确的
SET collation_connection=utf8_general_ci
3)如果还不行,那就是个别表或个别表的字段编码集错误,修改就可以
4)在my.ini中可以改变server的编码集,但一般不用。
附:mysql也给了sql语句中编码集的转变
http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html
Using COLLATE in SQL Statements
With the COLLATE clause, you can override whatever the default collation is for a comparison. COLLATE may be used in various parts of SQL statements. Here are some examples:
-
With ORDER BY:
SELECT k
FROM t1
ORDER BY k COLLATE latin1_german2_ci; -
With AS:
SELECT k COLLATE latin1_german2_ci AS k1
FROM t1
ORDER BY k1; -
With GROUP BY:
SELECT k
FROM t1
GROUP BY k COLLATE latin1_german2_ci; -
With aggregate functions:
SELECT MAX(k COLLATE latin1_german2_ci)
FROM t1; -
With DISTINCT:
SELECT DISTINCT k COLLATE latin1_german2_ci
FROM t1; -
With WHERE:
SELECT *
FROM t1
WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k;SELECT *
FROM t1
WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci; -
With HAVING:
SELECT k
FROM t1
GROUP BY k
HAVING k = _latin1 'Müller' COLLATE latin1_german2_ci;
现象:
当有mysql本地或远程建立function或procedure时报上面的错误
经试验是log_bin_trust_function_creators值为off导致
设置:set global log_bin_trust_function_creators=1;但重启后失效
永久windows下my.ini[mysqld]區段加上log_bin_trust_function_creators=1
linux下/etc/my.cnf下my.ini[mysqld]區段加上log_bin_trust_function_creators=1
很奇怪,我在linux下装过不会有这种问题啊!记一下。
3、mysql终端窗口function与procedure的建立
通常见到“;”系统会认为sql命令行结束,此时语法是
1. delimiter // --》注意//前有空格 把分隔符改成双杠,这样到中间 FROM T的时候mysql不会认为SQL命令已经结束了;
2. CREATE PROCEDURE p()
3. BEGIN
4. SELECT * FROM T;
5. END;//
6.
7. delimiter ;--》把分隔符再改回来
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/118838/viewspace-591223/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/118838/viewspace-591223/