Rhyous

November 5, 2009

How to create a UTF-8 Unicode Database on MySQL and make UTF-8 Unicode the default?

Filed under: FreeBSD,MySQL,Red Hat — J. Abram barneck @ 10:18 am
Tags: , , , ,

How to create a UTF-8 Unicode Database on MySQL?

I am not going to cover installing, I have done that here:
How to install MySQL on FreeBSD 7.2 or on Red Hat 5.4?

So when you open MySQL using the command line MySQL client, you can see what Character Set your server is configured to use with this command:

show variables like 'character_set_server';

Often the default is Latin-1. I wish UTF-8 was the default but it is not.

You can see the language your database is created with by using this command:

show create database dbname

Again, usually the default is Latin-1 and again, I wish the default were UTF-8 but it is not.

So how do I make my MySQL database UTF-8?
How do I make UTF-8 the default?

I am going to find out…

Ok, so I have MySQL installed on two different platforms:
FreeBSD 7.2 x64.
Red Hat 5.4 x64.

My question are these:
What level do you set the Unicode setting at? Install instance, database, or column type.

MySQL – Looks like it can be configured globally in the my.cnf or it can be database specific.

To configure globally

Add the following to the my.cnf file:

[mysqld]
init_connect=’SET collation_connection = utf8_general_ci’
init_connect=’SET NAMES utf8′
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake

Note: There are other options for collation besides utf8_general_ci such as utf8_unicode_ci. See this article:
http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-sets.html

Do I have to create the database in a special way?

Not if you configure the setting globally. However, if you don’t configure unicode support globally then yes you have to create your database in a specific way.

I found this post that is for an applications that uses a MySQL Unicode database. I don’t care about the application, just the MySQL data.
http://dev.mysql.com/doc/refman/5.1/en/create-database.html
http://dev.mysql.com/doc/refman/5.1/en/charset-applications.html

So the syntax will be:

CREATE DATABASE mydb   DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

Do I have to compile differently to get unicode support?

I didn’t have to recompile on either FreeBSD or Red Hat.

Is there differences for each platform?

Slight differences.

FreeBSD

FreeBSD has the MySQL client as a dependency so it gets installed with the server with out any extra work.

The Database folder is /var/db/mysql.

For the global configuration there is not a my.cnf file created by default.

FreeBSD has example my.cnf files located here:

/usr/local/share/mysql
/usr/local/share/mysql/my-huge.cnf
/usr/local/share/mysql/my-innodb-heavy-4G.cnf
/usr/local/share/mysql/my-large.cnf
/usr/local/share/mysql/my-medium.cnf
/usr/local/share/mysql/my-small.cnf

You can create your own my.cnf or you can copy one of the examples.

In order to get the my.cnf to work, you should copy it and change the owner and add the [mysqld] settings.

#
#
#
?
?
?
?
?
?
?
?
#
cp /usr/local/share/mysql/my-medium.cnf /var/db/mysql/my.cnf
chown mysql:mysql /var/db/mysql/my.cnf
cat << EOF >> /var/db/mysql/my.cnf
[mysqld]
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
EOF

Red Hat

Red Hat does not have the MySQL client installed with the server, you have to download a separate RPM and install it. But it is really easy. Download both RPMs and install them.

The Database folder is /var/lib/mysql.

For the global configuration there is not a my.cnf file created by default.

Red Hat has example my.cnf files located here:

/usr/share/mysql
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf

Same as FreeBSD, there isn’t one used by default and you have to copy one and use it.
You can create your own my.cnf or you can copy one of the examples.

In order to get the my.cnf to work, you should copy it and change the owner and add the [mysqld] settings.

#
#
#
?
?
?
?
?
?
?
?
#
cp /usr/share/mysql/my-medium.cnf /var/lib/mysql/my.cnf
chown mysql:mysql /var/lib/mysql/my.cnf
cat << EOF >> /var/lib/mysql/my.cnf
[mysqld]
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
EOF


Copyright ® Rhyous.com – Linking to this article is allowed without permission and as many as ten lines of this article can be used along with this link. Any other use of this article is allowed only by permission of Rhyous.com.

Advertisements

8 Comments »

  1. […] Configure MySQL to be Unicode. How to create a UTF-8 Unicode Database on MySQL and make UTF-8 Unicode the default?Note: […]

    Pingback by How to install dotProject 2.1.2 on FreeBSD 7.2 with Apache 2.2, PHP5, and MySQL 5.1 Server? « Rhyous's 127.0.0.1 or ::1 — January 19, 2010 @ 12:48 pm | Reply

  2. […] Configure MySQL to be Unicode. How to create a UTF-8 Unicode Database on MySQL and make UTF-8 Unicode the default?Note: […]

    Pingback by How to install dotProject 2.1.2 on FreeBSD 7.2 with Apache 2.2, PHP5, and MySQL 5.1 Server? « Rhyous's 127.0.0.1 or ::1 — January 19, 2010 @ 12:48 pm | Reply

  3. Very useful documentation. Keep up the good work.

    Comment by Warren — February 24, 2010 @ 7:48 am | Reply

  4. To create a db with UT8 unicode, why not utf8_unicode_ci instead of utf8_general_ci?

    Comment by Larry — March 19, 2010 @ 2:20 am | Reply

  5. Thanks a lot for the information. How to i retrive the data and print in unicode?

    Comment by Ranbir — August 28, 2011 @ 4:35 am | Reply

    • Usually your application is already setup to do this. However, if you are the one writing that application, you need to look at your development language APIs for whatever language you are developing in.

      Comment by Rhyous — August 28, 2011 @ 8:01 pm | Reply

  6. […] Configure MySQL to be Unicode. How to create a UTF-8 Unicode Database on MySQL and make UTF-8 Unicode the default?  […]

    Pingback by Freebsd8 bugzilla install from ports | Technical Blog of Dmitry Kireev — January 9, 2012 @ 5:42 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: