MySQL Collation utf8 differences

But I've been reading up on the importance of MySQL Collation and what I have learned so far regarding compatibility and accuracy is these 4 seem to be my best bet.

• utf8_unicode_ci
• utf8_unicode_520_ci
• utf8mb4_unicode_ci
• utf8mb4_unicode_520_ci

From my understanding uft8mb4 would be good for character with mutli language (character) support (japanese for example). uf8 only supports 3 bytes while uf8mb4 supports 4 bytes. So it sounds like the obvious choice would be uf8mb4, but the catch seems to be that you have a length limit (Damn it! I want my cake and eat it too), which is a little concern (I think).

Then you take into account about the 520 standard; which offers more, from what little I could find on it. But that is of course the issue, I could find very little about it. Only that people say it's an improvement, yet being very vague on how that is.

I do want the most I can get with as few restrictions as possible... I figured someone here may know a thing or two.

Of the 4 which would offer the most compatibility, accuracy, and the most storage length? Also what truly is the big difference between the 502 standard?

 

I decided to use

• utf8mb4_unicode_520_ci

Being that the utf8mb4 added more character support and this one uses the newer 520 standard (unless anyone can think of a good reason not to do so).

Normally converting a database takes time because you need to do it table by table. This little bit of code makes that process a lot quicker.

<?php

$database_name='DATABASE-NAME-HERE';
$database_username='USER-NAME-HERE';
$database_password='PASSWORD-HERE';

$connection = mysql_connect('localhost',$database_username,$database_password);

if(!$connection) {
    echo "Cannot connect to the database – incorrect details";
} else{
    mysql_select_db($database_name);
    $result=mysql_query('show tables');

    while($tables = mysql_fetch_array($result)) {

        foreach ($tables as $key => $value) {
            mysql_query("ALTER TABLE ".$value." CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci");
        }
    }
echo "Successfull collation change!";
}
?>

PHP:

Change the values to fit your needs. (The above example changed everything to use uft8mb4. You should change that also to fit your needs)

Run this from the terminal.

php  /path/to/your/FILE-Name.php

PHP:

Obviously changing the path and file name to match what you're using.

Finally you should run this command

mysqlcheck -u root -p --auto-repair --optimize --all-databases

PHP: