Character encoding in MySQL

Just a quick note on character encoding in MySQL, use the following to force MySQL into using UTF-8 for ever!

PHP

In your PHP, always use the following immediately after you set up your connection.

• mysql_set_charset(‘utf8′, $oDB);
• mysql_query(‘SET NAMES \’utf8\”);

The tricky bit lies in your MySQL configuration file:

my.cnf

Add the following lines:

[client]
default-character-set=utf8
[mysqld]
default-character-set=utf8
default-collation = utf8_general_ci

This tells MySQL that the client (which is either PHP or the system) expects UTF-8. If you don’t tell MySQL this, a mysqldump will produce ANSI and an import from the command line will result in double encodings.

And don’t forget to use utf8_general_ci for all databases, tables and fields!

MySQL Substr count

Recently I needed to create an SQL statement which was to count the occurences of a character inside a particular database field. For example, you could store the tags connected to a single db-record in a semi-colon (;) separated column in the record. To get the number of tags for this record straight from the database, we would thus like the number of semi-colons in the field (+1) as an indicator for this count. Since MySQL5 does not have a standard function for this (similar to PHP’s substr_count()), I had to find something of my own. Read the rest of this entry »

MySQL and Integers

Also tired of always looking up the minimum and/or maximum value of a MySQL integer field? For once and for all a cheatsheet: