1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL Collation

Discussion in 'General PHP and MySQL Discussions' started by Adam Howard, May 24, 2014.

  1. Adam Howard

    Adam Howard Well-Known Member

    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.
     
  2. Brogan

    Brogan XenForo Moderator Staff Member

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

    XenForo sets each table to utf8_general_ci during install.
     
    SneakyDave likes this.
  3. Adam Howard

    Adam Howard Well-Known Member

    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
     
    Last edited: May 25, 2014
  4. Adam Howard

    Adam Howard Well-Known Member

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


    PHP:
    <?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
     
    Last edited: May 25, 2014
  5. Adam Howard

    Adam Howard Well-Known Member

    Suggest you also run this command after making changes
    PHP:
    mysqlcheck -u root ---auto-repair --optimize --all-databases
     
    Last edited: May 25, 2014
  6. Adam Howard

    Adam Howard Well-Known Member

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

    /install/index.php?upgrade/rebuild
     

Share This Page