在SQL敘述中使用COLLATE

這是MySQL 5實力養成暨評量裡的6-08.『MySQL的校對(collation)設定，主要是用來對於字串作哪些處理時使用？』

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

以及

除了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 '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;

點我分享到Facebook