mysql问题总结

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/