Showing posts with label Mysql Charset. Show all posts
Showing posts with label Mysql Charset. Show all posts

Wednesday, 14 November 2018

Convert latin1 to UTF-8 in MySQL

Since MySQL 4.1, UTF-8 is the default charset. If you have an old database, containing data encoded in latin1, and you want upgrade to a newer MySQL server, then you can do the following if you want all your data in UTF-8 instead:
Be careful when switching to UTF-8. Once you have converted your data, any program/webapp that uses the database will have to check that the data they are sending to the database is valid UTF-8. If it isn't then MySQL will silently truncate the data after the invalid part, which can cause all sorts of problems. If your program/webapp doesn't specifically say that it supports unicode then you may want to stick with latin1 instead.

MySQL dump

First of all, we need to dump the old data into a file.
mysqldump -h example.org --user=foo -p --default-character-set=latin1 -c \ --insert-ignore --skip-set-charset -r dump.sql dbname
Please note: You have to replace the user, the host and the dbname, otherwise it won't connect to your database.

Convert dump

Before the next step, it'd be useful to run the following command to check the current charset in your dump file
file dump.sql
If your file is already in UTF-8, you may need to skip the following iconv command.
Next thing to do is, converting the characters in the MySQL dump from latin1 to UTF-8
iconv -f ISO8859-1 -t UTF-8 dump.sql > dump_utf8.sql
If you see after your conversion, that umlauts in your database are converted correctly, but that the sign ß and € are broken, you might get it working by using -f CP1252 instead of -f ISO8859-1 in this command.
perl -pi -w -e 's/CHARSET=latin1/CHARSET=utf8/g;' dump_utf8.sql
There may be other places in your database, where latin-1 character set is used. For example, there may be lines in your dump similar to:
ar_title varchar(255) character set latin1 collate latin1_bin NOT NULL default
The best is to either grep your dump against the word "latin1" or look for those lines in some text editor (don't forget to use case-insensitive search).
If you have another source charset, you need to replace the -f option with your local character set.

Drop and create

Now it's time to drop the old database and create a new one with UTF-8 support.
mysql --user=foo -p --execute="DROP DATABASE dbname; CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;"
(MySql seems to recommend utf8_unicode_ci over utf8_general_ci for 5.1 +, see http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-sets.html)

Import dump to database

Last but not least, we need to import the converted data back to the new database.
mysql --user=foo --max_allowed_packet=16M -p --default-character-set=utf8 dbname < dump_utf8.sql
The max_allowed_packet option is sometimes important. If your import ends up with a "ERROR 1153 at line 42: Got a packet bigger than 'max_allowed_packet'", you need to increase the packet size. To do this, edit /etc/mysql/my.cnf and set max_allowed_packet=16M under the [mysqld] directive. This is because utf-8 coded characters will take more space than latin-1.
Dont forget to restart your mysql server.

Alternative Method

Instead of using the above method of dumping and reloading your data, you can use the ALTER TABLE/DATABASE SQL statements to convert your data.
For each table you want to convert to utf8, use the following statement.
ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8;
This will automatically convert all text columns to utf8.
To set the default character set for a database, use the following statement.
ALTER DATABASE <database_name> CHARACTER SET utf8;
This does not affect any existing tables, but any future tables created in this database will use utf8 by default.
"The CONVERT TO operation converts column values between the character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like utf8). In this case, you have to do the following for each such column:
ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;
The reason this works is that there is no conversion when you convert to or from BLOB columns."

Alternative Method 2

When the data has been double-encoded, none of the methods above will work. You will still have funny looking characters in the database. Someone provided the following solution on his blog and it works very well
mysqldump -h DB_HOST -u DB_USER -p DB_PASSWORD --opt --quote-names \ --skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql
mysql -h DB_HOST -u DB_USER -p DB_PASSWORD \ --default-character-set=utf8 DB_NAME < DB_NAME-dump.sql

Friday, 2 November 2018

Change MySQL default character set to UTF-8 in my.cnf?

Currently we are using the following commands in PHP to set the character set to UTF-8 in our application.
Since this is a bit of overhead, we'd like to set this as the default setting in MySQL. Can we do this in /etc/my.cnf or in another location?
SET NAMES 'utf8'
SET CHARACTER SET utf8
I've looked for a default charset in /etc/my.cnf, but there's nothing there about charsets.
At this point, I did the following to set the MySQL charset and collation variables to UTF-8:
skip-character-set-client-handshake
character_set_client=utf8
character_set_server=utf8
Is that a correct way to handle this?

 Answers


To set the default to UTF-8, you want to add the following to my.cnf
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8


[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
If you want to change the character set for an existing DB, let me know... your question didn't specify it directly so I am not sure if that's what you want to do.


On MySQL 5.5 I have in my.cnf
[mysqld] 
init_connect='SET collation_connection = utf8_unicode_ci' 
init_connect='SET NAMES utf8' 
character-set-server=utf8 
collation-server=utf8_unicode_ci 
skip-character-set-client-handshake
Result is
mysql> show variables like "%character%";show variables like "%collation%";

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)


I also have found out that after setting default-character-set = utf8 under [mysqld] title, MySQL 5.5.x would not start under Ubuntu 12.04 (Precise Pangolin).


MySQL v5.5.3 and greater:
Just add three lines only in the [mysqld] section:
[mysqld]
character-set-server = utf8
collation-server = utf8_unicode_ci
skip-character-set-client-handshake
Note: Including skip-character-set-client-handshake here obviates the need to include both init-connect in [mysqld] and default-character-set in the [client] and [mysql] sections.


Under Xubuntu 12.04 I simply added
[mysqld]
character_set_server = utf8
to /etc/mysql/my.cnf
And the result is
mysql> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)




On Fedora 21
$ vi /etc/my.cnf
Add follow:
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci 
skip-character-set-client-handshake
Save and exit.
Final remember restart service mysqld with service mysqld restart.


If you're having trouble confirming the client's character-set support using MySQL Workbench, then keep the following note in mind:
Important All connections opened by MySQL Workbench automatically set the client character set to utf8. Manually changing the client character set, such as using SET NAMES ..., may cause MySQL Workbench to not correctly display the characters. For additional information about client character sets, see Connection Character Sets and Collations.
Thus I was unable to override MySQL Workbench's character sets with my.cnf changes. e.g. 'set names utf8mb4'


You can do it the way it does, and if it doesn't work, you need to restart mysql.

How do I see what character set a MySQL database / table / column is?

What is the (default) charset for:
  • MySQL database
  • MySQL table
  • MySQL column

 Answers


Here's how I'd do it -
For Schemas:
SELECT default_character_set_name FROM information_schema.SCHEMATA 
WHERE schema_name = "schemaname";
For Tables:
SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "schemaname"
  AND T.table_name = "tablename";
For Columns:
SELECT character_set_name FROM information_schema.`COLUMNS` 
WHERE table_schema = "schemaname"
  AND table_name = "tablename"
  AND column_name = "columnname";


For databases:
USE your_database_name;
show variables like "character_set_database";
-- or:
-- show variables like "collation_database";
Cf. this page. And check out the MySQL manual


For tables:
SHOW TABLE STATUS will list all the tables.
Filter using:
SHOW TABLE STATUS where name like 'table_123';



SELECT TABLE_SCHEMA,
       TABLE_NAME,
       CCSA.CHARACTER_SET_NAME AS DEFAULT_CHAR_SET,
       COLUMN_NAME,
       COLUMN_TYPE,
       C.CHARACTER_SET_NAME
  FROM information_schema.TABLES AS T
  JOIN information_schema.COLUMNS AS C USING (TABLE_SCHEMA, TABLE_NAME)
  JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA
       ON (T.TABLE_COLLATION = CCSA.COLLATION_NAME)
 WHERE TABLE_SCHEMA=SCHEMA()
   AND C.DATA_TYPE IN ('enum', 'varchar', 'char', 'text', 'mediumtext', 'longtext')
 ORDER BY TABLE_SCHEMA,
          TABLE_NAME,
          COLUMN_NAME
;


For tables and columns:
show create table your_table_name


For databases:
SHOW CREATE DATABASE "DB_NAME_HERE";
In creating a Database (MySQL), default character set/collation is always LATIN, instead that you have selected a different one on initially creating your database

Mysql: What's the difference between utf8_general_ci and utf8_unicode_ci

Between utf8_general_ci and utf8_unicode_ci, are there any differences in terms of performance?

Answers


These two collations are both for the UTF-8 character encoding. The differences are in how text is sorted and compared.
Note: Since MySQL 5.5.3 you should use utf8mb4 rather than utf8. They both refer to the UTF-8 encoding, but the older utf8 had a MySQL-specific limitation preventing use of characters numbered above 0xFFFD.
  • Accuracy
    utf8mb4_unicode_ci is based on the Unicode standard for sorting and comparison, which sorts accurately in a very wide range of languages.
    utf8mb4_general_ci fails to implement all of the Unicode sorting rules, which will result in undesirable sorting in some situations, such as when using particular languages or characters.
  • Performance
    utf8mb4_general_ci is faster at comparisons and sorting, because it takes a bunch of performance-related shortcuts.
    On modern servers, this performance boost will be all but negligible. It was devised in a time when servers had a tiny fraction of the CPU performance of today's computers.
    utf8mb4_unicode_ci, which uses the Unicode rules for sorting and comparison, employs a fairly complex algorithm for correct sorting in a wide range of languages and when using a wide range of special characters. These rules need to take into account language-specific conventions; not everybody sorts their characters in what we would call 'alphabetical order'.
As far as Latin (ie "European") languages go, there is not much difference between the Unicode sorting and the simplified utf8mb4_general_ci sorting in MySQL, but there are still a few differences:
  • For examples, the Unicode collation sorts "ß" like "ss", and "Œ" like "OE" as people using those characters would normally want, whereas utf8mb4_general_ci sorts them as single characters (presumably like "s" and "e" respectively).
  • Some Unicode characters are defined as ignorable, which means they shouldn't count toward the sort order and the comparison should move on to the next character instead. utf8mb4_unicode_ci handles these properly.
In non-latin languages, such as Asian languages or languages with different alphabets, there may be a lot more differences between Unicode sorting and the simplified utf8mb4_general_ci sorting. The suitability of utf8mb4_general_ci will depend heavily on the language used. For some languages, it'll be quite inadequate.
What should you use?
There is almost certainly no reason to use utf8mb4_general_ci anymore, as we have left behind the point where CPU speed is low enough that the performance difference would be important. Your database will almost certainly be limited by other bottlenecks than this.
The difference in performance is only going to be measurable in extremely specialised situations, and if that's you, you probably already know about it. If you're experiencing slow sorting, in almost all cases it'll be an issue with your indexes/query plan. Changing your collation function should not be high on the list of things to troubleshoot.
In the past, some people recommended to use utf8mb4_general_ci except when accurate sorting was going to be important enough to justify the performance cost. Today, that performance cost has all but disappeared, and developers are treating internationalization more seriously.
One other thing I'll add is that even if you know your application only supports the English language, it may still need to deal with people's names, which can often contain characters used in other languages in which it is just as important to sort correctly. Using the Unicode rules for everything helps add peace of mind that the very smart Unicode people have worked very hard to make sorting work properly.


This post describes it very nicely.
In short: utf8_unicode_ci uses the Unicode Collation Algorithm as defined in the Unicode standards, whereas utf8_general_ci is a more simple sort order which results in "less accurate" sorting results.


In brief words:
If you need better sorting order - use utf8_unicode_ci (this is the preferred method),
but if you utterly interested in performance - use utf8_general_ci, but know that it is a little outdated.
The differences in terms of performance are very slight.

Subscribe to: Posts (Atom)