在SQL叙述中使用COLLATE

By:
On:
In: MySQL
Tagged: ,

2013-02-26_090904

这是MySQL 5实力养成暨评量里的6-08.‘MySQL的校对(collation)设定，主要是用来对于字串作哪些处理时使用？’

答案是(B) SELECT中的Order by (C) SELECT中的Group by

[adsense][/adsense]

朋友们可以参阅MySQL 5.6 Reference Manual :: 10 Globalization :: 10.1 Character Set Support :: 10.1.7 Collation Issues :: 10.1.7.2 Using COLLATE in SQL Statements

以及

MySQL 5.1参考手册 :: 10. 字符集支持::10.3. 确定默认字符集和校对::10.3.8. 在SQL语句中使用COLLATE

除了ORDER BY跟GROUP BY之外，文中还有举例AS、聚合函式、DISTINCT、WHERE、HAVING等使用：

  • 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 'Muller' COLLATE latin1_german2_ci = k;
         SELECT *
     FROM t1
     WHERE k LIKE _latin1 'Muller' COLLATE latin1_german2_ci;
  • With HAVING: 
    SELECT k
FROM t1
GROUP BY k
HAVING k = _latin1 'Muller' COLLATE latin1_german2_ci;