Geek asks and answers

How can I manage a UTF-8 order in MySQL?

For example, this is the order returned by the standard SORT BY name query:

name
------
Cebbb
Čebbc
Cebbd

I would like to first specify the name SORT BY and first get the accented character grouped with other accented characters, like

name
------
Čebbc
Cebbb
Cebbd

By default, MySql processes Čas if it were C, to sort and sort in that order.

Alternatively, is there a way in PHP that I can “convert” Čto Cfor comparison?

+5
source share
4 answers

You can add an expression COLLATEto the sentenceORDER BY :

SELECT k
FROM t1
ORDER BY k COLLATE utf8_spanish_ci;

, , .

+6

- , :

CREATE TABLE foo (
    foo_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(30) NOT NULL COLLATE 'utf8_spanish_ci',
    PRIMARY KEY (`foo_id`)
)
COLLATE='utf8_spanish_ci'
ENGINE=InnoDB;

Update:

Č :

, , , . , , . . ( چ) .

, . - . SQL-, , MySQL:

SHOW CHARACTER SET

... , . .

+2

, , php iconv:

iconv("UTF-8", "ASCII//TRANSLIT", $text)

$text ASCII. , müßig muessig caffee.

0

, , , , :

ORDER BY SUBSTRING(name, 1, 1),
         BINARY SUBSTRING(name, 1, 1),
         name

, , - , , , :

  • ( MySql Č C )
  • Then ordering by the binary value of the starting character, which will distinguish ČandC
  • It is then sorted by the full name, which will - in essence - be ordered by the rest of the string.

This will be uncontrollable after the first character, but that is not a problem.

0
source

More articles:


All Articles