ITPub博客

首页 > Linux操作系统 > Linux操作系统 > mysql问题总结

mysql问题总结

原创 Linux操作系统 作者:grazier 时间:2009-04-21 23:13:15 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 思路点滴
请登录后发表评论 登录
全部评论

注册时间:2008-10-20

  • 博文量
    53
  • 访问量
    219853