I'm wondering if there is a "best" choice for collation in MySQL for a general website where you aren't 100% sure of what will be entered? I understand that all the encodings should be the same, such as MySQL, Apache, the HTML and anything inside PHP. In the past I have set PHP to output in "UTF-8", but which collation does this match in MySQL? I'm thinking it's one of the UTF-8 ones, but I have used <code>utf8_unicode_ci</code>, <code>utf8_general_ci</code>, and <code>utf8_bin</code> before.

Actually, you probably want to use <code>utf8_unicode_ci</code> or <code>utf8_general_ci</code>. <ul> <li> <code>utf8_general_ci</code> sorts by stripping away all accents and sorting as if it were ASCII</li> <li> <code>utf8_unicode_ci</code> uses the Unicode sort order, so it sorts correctly in more languages</li> </ul> However, if you are only using this to store English text, these shouldn't differ.

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. <code>utf8_general_ci</code> is somewhat faster than <code>utf8_unicode_ci</code>, but less accurate (for sorting). The specific language utf8 encoding (such as <code>utf8_swedish_ci</code>) contain additional language rules that make them the most accurate to sort for those languages. Most of the time I use <code>utf8_unicode_ci</code> (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

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

Tags:

php

mysql

encoding

collation

I'm wondering if there is a "best" choice for collation in MySQL for a general website where you aren't 100% sure of what will be entered? I understand that all the encodings should be the same, such as MySQL, Apache, the HTML and anything inside PHP.

In the past I have set PHP to output in "UTF-8", but which collation does this match in MySQL? I'm thinking it's one of the UTF-8 ones, but I have used utf8_unicode_ci, utf8_general_ci, and utf8_bin before.

990

asked Dec 15 '08 07:12

Darryl Hein

2 Answers

Actually, you probably want to use utf8_unicode_ci or utf8_general_ci.

utf8_general_ci sorts by stripping away all accents and sorting as if it were ASCII
utf8_unicode_ci uses the Unicode sort order, so it sorts correctly in more languages

However, if you are only using this to store English text, these shouldn't differ.

answered Oct 21 '22 09:10

Vegard Larsen

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

112

answered Oct 21 '22 10:10

Eran Galperin

Related questions
                            
                                How can I get useful error messages in PHP?
                            
                                Preferred method to store PHP arrays (json_encode vs serialize)
                            
                                Cannot use object of type stdClass as array?
                            
                                How can I capture the result of var_dump to a string?
                            
                                Show a number to two decimal places
                            
                                Formatting a number with leading zeros in PHP [duplicate]
                            
                                Pretty-Printing JSON with PHP
                            
                                Fatal Error: Allowed Memory Size of 134217728 Bytes Exhausted (CodeIgniter + XML-RPC)
                            
                                Are PDO prepared statements sufficient to prevent SQL injection?
                            
                                How do I strip all spaces out of a string in PHP? [duplicate]
                            
                                Finding the number of days between two dates
                            
                                How do I get the query builder to output its raw SQL query as a string?
                            
                                Create a folder if it doesn't already exist
                            
                                define() vs. const
                            
                                PHP parse/syntax errors; and how to solve them
                            
                                SQL injection that gets around mysql_real_escape_string()
                            
                                What are the differences in die() and exit() in PHP?
                            
                                How do I send a POST request with PHP?
                            
                                How do I pass variables and data from PHP to JavaScript?
                            
                                How to calculate the difference between two dates using PHP?

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!

Donate Us With