Geek asks and answers

MySQL query - case sensitivity with ORDER BY rand ()

Is it possible to force a register for a query?

Mine sounds like this:

"SELECT g_path FROM glyphs WHERE g_glyph = :g_glyph ORDER BY rand()"

if g_glyph = r, the result can be R or r, and this is not what I expect. I am looking for case sensitive feedback.

I searched my problem and found this solution:

/*Case-sensitive sort in descending order.
In this query, ProductName is sorted in 
case-sensitive descending order.
*/
SELECT ProductID, ProductName, UnitsInStock
FROM products
ORDER BY BINARY ProductName DESC;

But the following line does not work at all:

"SELECT g_path FROM glyphs WHERE g_glyph = :g_glyph ORDER BY BINARY rand()"

Any suggestion?

Many thanks for your help.

+3
source share
2 answers

The order and equality of characters is determined by collation . In most cases, case-insensitive sorting is used.

If you need to use strict case-sensitive matching for a specific binding, use the operator BINARY:

mysql> SELECT 'a' = 'A';
        -> 1
mysql> SELECT BINARY 'a' = 'A';
        -> 0
mysql> SELECT 'a' = 'a ';
        -> 1
mysql> SELECT BINARY 'a' = 'a ';
        -> 0

So in your case:

SELECT g_path FROM glyphs WHERE BINARY g_glyph = :g_glyph ORDER BY rand()
+5

.

.

- latin1 latin1_swedish_ci, . , col_name LIKE 'a%' , A a. , , . , , latin1, COLLATE, latin1_general_cs latin1_bin:

col_name COLLATE latin1_general_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_bin

, , . . 13.1.14, " CREATE TABLE".

_cs " ".

+2

More articles:


All Articles