Collations in MySQL and Laravel Database Settings


by Samarth Sinha on 2017-02-12 18:43:14

A collation is a set of rules that defines how to compare and sort character strings. Each collation in MySQL belongs to a single character set. Every character set has at least one collation, and most have two or more collations.

A collation orders characters based on weights. Each character in a character set maps to a weight. Characters with equal weights compare as equal, and characters with unequal weights compare according to the relative magnitude of their weights.

Some of the common collation types are: latin1_swedish_ci, utf8_unicode_ci, utf8_general_ci, utf8mb4_general_ci, etc. The suffix in the collation name has the following meaning.

        _ai     Accent insensitive
        _as     Accent sensitive
        _ci     Case insensitive
        _cs     Case sensitive
        _bin     Binary

In Laravel the default collation upto version 5.3 was utf8_unicode_ci in version 5.4 the default collation is utf8mb4_unicode_ci. When using utf8mb4, characters count as 4 bytes, whereas under utf8, they could as 3 bytes. InnoDB databases have a limit that Indexes can only contain 767 bytes. So when using utf8, you can store 255 characters (767/3 = 255), but using utf8mb4, you can only store 191 characters (767/4 = 191). So you cannot create indexing or keys for field which contain more than 191 characters.

For making keys for field 255 charecters change the 'collation' in database.php file in your config folder to utf8_unicode_ci and you are good to go.

About the Author

  • avatar
    Samarth Sinha

    My name Samarth Sinha. I work on developing Web Applications using PHP and MySQL and in different frameworks of PHP like Laravel, CakePHP, CodeIgnitor and Smarty. In databases I have worked in MySQL and Postgres. I started this blog to share my knowledge to those who wants to start developing Web Applications using PHP.