Archive for mysql

Rails NULL Encoding of MySQL Results

If you are not careful, the encoding that you set in database.yml may bring some ill-effects. The encoding: utf8 causes ActiveRecord connection to execute set names = 'utf8' in the MySQL session. This tells MySQL server that the this particular session sends and want to receive everything in utf8 character set.

Even the column stored in ‘latin1’ is converted to ‘utf8’. This takes a considerable hit on the performance, I also have a suspicion that MySQL does not cache results when it is forced to do the conversion.

How to avoid

1. Store data in correct encoding as it needs.

2. By telling MySQL to not convert the results by initaliazing rails with…

ActiveRecord::Base.connection.execute('SET CHARACTER_SET_RESULTS = NULL')


MySQL: Encoding issues – latin1 to utf8 – easy

There are many reasons why you should get the encoding right in the mysql columns. Unfortunately when you started it, the databases default encoding was in latin1 and now you want to convert to utf8 as per your needs. You can easily do it within the mysql.

Lets do this using an example. For this purpose we have a table called news. This used to have default encoding as latin1, so the column title varchar(255) and content text were storing values in latin1.

To change the values from latin1 to utf8 for the two string columns:

Step 1: set the original default encoding to the columns:

alter table news 
       modify title varchar(255) character set 'latin1', 
       modify content text character set 'latin1';

Step 2: now change the column type to binary types:

alter table news 
       modify title varbinary(255),
       modify content varbinary(65536);

Step 3: now convert to utf8:

alter table news 
        modify title varchar(255) character set 'utf8',
        modify content text character set 'utf8';

This is it, you should be all converted from latin1 to utf8. Also remember to pass the original attributes of the those columns again when using modify column.

Comments (1)