MySQL Collation

[Adam Howard]

I've never really bothered much when it came to the 'collation' setting in MySQL. But I noticed I had a lot of mixed values. Including some old latin value. I've read up a little and I've come down to these 4

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 sound 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.

So being that I do want to learn and I do want the most I can get with as few restrictions as possible... I figured someone here may know a thing or two.

 

[Paul B]

I always set mine as utf8_general_ci when creating a new database instance.

XenForo sets each table to utf8_general_ci during install.

 

[Adam Howard]

I always set mine as utf8_general_ci when creating a new database instance.

XenForo sets each table to utf8_general_ci during install.

I've decided to move away from utf8_general_ci. From what I understand utf8_general_ci is a little faster, but unicode is more accurate. And the difference in speed is really insignificant and I'd rather the accuracy

 

[Adam Howard]

Rather than convert each table 1 by 1 manually, which can be VERY time consuming. This may come in handy for some people.

<?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!";
}
?>

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

Run this from the terminal.

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

 

[Adam Howard]

Suggest you also run this command after making changes

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

 

[Adam Howard]

I do not think it will be required. But for good measure I rebuilt the master data

/install/index.php?upgrade/rebuild

 

[CoZmicShReddeR]

Hello, I am trying to change my entire database collation was wondering if there was an updated version of this? I tried it doesn't work..

 

[S Thomas]

https://xenforo.com/xf2-docs/manual/unicode/#upgrading-existing-installations
Should be the easiest run

 

[CoZmicShReddeR]

So utf8mb4_unicode_520_ci

I use?
php cmd.php xf:convert-utf8mb4_unicode_520_ci

 

[Chris D]

XF2 only supports utf8mb4_general_ci

The command to convert is:

php cmd.php xf:convert-utf8mb4

 

[CoZmicShReddeR]

XF2 only supports utf8mb4_general_ci

The command to convert is:

php cmd.php xf:convert-utf8mb4

Wow I am glad I asked that then!

 

[14DH01]

Hello's
To remember the command when I go to 'xf2'