What Is the Best Collation to Use For MySQL With PHP

What is the best collation to use for MySQL with PHP?

The main difference is sorting accuracy (when comparing characters in the language) and performance. The only special one is utf8_bin which is for comparing characters in binary format.

utf8_general_ci is somewhat faster than utf8_unicode_ci, but less accurate (for sorting). The specific language utf8 encoding (such as utf8_swedish_ci) contain additional language rules that make them the most accurate to sort for those languages. Most of the time I use utf8_unicode_ci (I prefer accuracy to small performance improvements), unless I have a good reason to prefer a specific language.

You can read more on specific unicode character sets on the MySQL manual - http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html

In MySQL, which collation should I choose?

Collation is not actually the default, it's giving you the default collation as the first choice.

What we're talking about is collation, or the character set that your database will use in its text types. Your default option is usually based on regional settings, so unless you're planning to globalize, that's usually peachy-keen.

Collations also determine case and accent sensitivity (i.e.-Is 'Big' == 'big'? With a CI, it is). Check out the MySQL list for all the options.

Better collation option to support all languages in MYSQL

If I were starting a project today with MySQL 8.0, I'd choose this as a default:

character set: utf8mb4

collation: utf8mb4_0900_ai_ci

(reportedly this collation does not work for Canadian French)

See also: https://www.percona.com/live/e17/sites/default/files/slides/Collations%20in%20MySQL%208.0.pdf

What collation should be used for french language?

Just use UTF-8.

Add in the top of the HTML page:

<meta http-equiv="Content-type" value="text/html; charset=UTF-8" />

and in the PHP page:

header('Content-type: text/html; charset=utf-8');

in your DB launch the query:

mysqli_query('SET CHARACTER SET utf8');

it should work!

---

Related Topics