mysql问题总结

最新推荐文章于 2019-09-15 10:13:14 发布

转载最新推荐文章于 2019-09-15 10:13:14 发布 · 125 阅读

1、mysql 1267错误
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

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;

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 ;--》把分隔符再改回来

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/118838/viewspace-591223/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/118838/viewspace-591223/