Inserting Unicode into MySQL

If Unicodes end up as funny characters after inserting into MySQL, then try executing SET NAMES ‘utf8′ immediately after obtaining a DB connection. For example:

$link = mysql_connect(‘localhost’,'myuser’,'mypassword’);
mysql_query(“SET NAMES ‘utf8′”);
mysql_select_db(‘mydb’);

References: http://www.adviesenzo.nl/examples/php_mysql_charset_fix/

This entry was posted on Tuesday, August 26th, 2008 at 2:58 am and is filed under mysql, php. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Inserting Unicode into MySQL”

  1. Choong Yong Says:
    September 3, 2008 at 11:41 am | Reply

    Hi Sing Chyun, I am not sure about the mysql client side on PHP, but it is actually a good idea to make your mysql database use unicode by default:

    CREATE DATABASE my_database DEFAULT CHARACTER SET utf8;

    and create tables with the following:

    ENGINE=InnoDB DEFAULT CHARSET=utf8;

    If you already have existing database, either back up, drop the database and create with the above steps, or alter the table with:

    ALTER TABLE `my_table` CHARACTER SET utf8 COLLATE utf8_general_ci;

    For some text columns, you might need to alter them as follows too:

    ALTER TABLE `my_table` MODIFY COLUMN `my_column` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL default ”;

    Hope that helps. :)

  2. singchyun Says:
    September 4, 2008 at 3:48 pm | Reply

    Hi Choong Yong, thank you for the comprehensive steps!

Leave a Reply