The problems being addressed
⚈ Your web page the wrong characters coming out where accented letters should be.
⚈ You are upgrading from MySQL 4.0, where characters were simply bytes.
⚈ You are trying to wrap your head around how MySQL handles CHARSET / COLLATION.
⚈ You upgraded, and now you are getting garbage.
Please read most of this document. I know it is long (after all, I wrote it!)
But you really need to understand a lot of the pieces in it, in order
to solve your problem.
Basic Concepts
"Character" and "byte" are different!
You must understand this before continuing.
A "byte" is an a-bit thing; it is the unit of space in computers (today).
A "character" is composed of one or more bytes, and represents what we think
of when reading.
A byte can represent only 256 different values.
There are over 11,000 Korean characters and over 40,000 Chinese characters --
no way to squeeze such a character into a single byte.
Charset vs collation. These are different things!
'Charset' ('character set'; 'encoding') refers to the bits used to represent 'characters'.
'Collation' refers to how those bits could be compare for inequality (WHERE)
and sorting (ORDER BY). GROUP BY and FOREIGN KEY CONSTRAINTS can also involve collation.
And it even can involve deciding whether two different bit strings compare 'equal'.
History
1950's -- A character was (sometimes) represented in only 5 bits, on "paper tape"; no lowercase; had to "shift" to switch between letters and digits.
1960's -- A character was 6 bits; no lower case. "Punched cards" were used.
1970's -- 7-bit ASCII becomes common -- that limits you to English.
And the 8-bit "byte" was invented and was coming into common usage (re: IBM 360).
1980's -- Beginning to see 8-bit encodings. Especially since 7-bit ASCII
was wasting a bit of the omni-present "byte".
This can handle Western European accented letters.
1990's -- The computer world realizes that there are other people in
the world and embarks on Unicode and UTF8.
Meanwhile, MySQL is born,
but has enough problems without worrying about character sets.
Through version 4.0, a CHAR is just a byte.
You can put any kind of bytes, representing anything, into a VARCHAR.
Thus, begins the need for this discussion.
MySQL 4.1 introduced the concept of "character set" and "collation".
If you had legacy data or legacy code, you probably did not notice
that you were messing things up when you upgraded.
Or you started with 4.1 (or later) and "latin1 / latin1_swedish_ci"
and failed to notice that you were asking for trouble.
Today, it's pretty safe to simply lay down the law and say
"Use utf8 for all text."
Sure, there are other character sets,
and they are useful if you have a narrow focus.
But, you may as well use utf8.
Best Practice
Best practice is to go completely utf8.
I will focus on utf8, but if you choose to do otherwise, keep reading;
most of this discussion can still be adapted to the charset of your choice.
A web page (if that is what you have) should begin with
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
Establish the characterset for talking to the MySQL server:
⚈ init_connect = 'SET NAMES utf8' in my.cnf
⚈ SET NAMES utf8 -- done immediately after connecting
⚈ SET GLOBAL VARIABLE character_set_... SET NAMES does the three you need.
For PHP:
⚈ mysql interface: mysql_set_charset('utf8'); (assuming PHP 5.2.3 & MySQL 5.0.7) Note: "mysql_" interface is deprecated.
⚈ mysqli interface: mysqli_set_charset('utf8') function. See mysqli_set_charset
⚈ PDO interface: set the charset attribute of the PDO dsn.
(from #29 in Devshed's PHP tips)
SET NAMES can be invoked by your language just like
other non-SELECT commands: mysqli_query(), do(), execute(), etc.
I digress here for a note about GRANTs.
'root', and any other user from GRANT ALL ON *.*,
because it has "SUPER" privilege, will skip the init_connect,
so that technique is not 'perfect'.
SET NAMES
Declare most CHAR/TEXT columns in all tables as CHARSET utf8.
Some exceptions: columns with hex (MD5, GUID), ...
These should be BINARY or CHAR charset ASCII.
Collation?
For true text, utf8_unicode_ci is best. It handles the complex
rules of "combining chararacters", etc.
It handles essentially all languages simultaneously, but compromises on ordering.
utf8_general_ci is the default for utf8, so you may accidently
get this. It is a little faster than utf8_unicode_ci and
works ok for many situations.
utf8_bin says to just compare the bytes.
CHAR/TEXT utf8 with utf8_bin validates (on INSERT) that the bytes comprise valid
utf8 bytes, but does not do anything useful for comparisions other than
exact (no case folding, etc) equality.
BINARY/BLOB should be usually be used instead CHAR+utf8;
this stores the bytes without any checking.
Alas, MySQL's collations think of case folding and accent-stripping as equivalent.
That is, there is no utf8 collation for case folding, but keeping accents distinct.
Do SHOW COLLATION LIKE '%cs'; to see the few collations that work that way.
Example, using PHP
Arabic in mysql tables
When do bytes get translated?
If you do all the "right things", you will accidentally have a "correctly"
performing application. You need to understand when and how bytes
are modified, in order to fix existing problems and to build your
application correctly.
Short answer: You tell MySQL how the bytes in your application are encoded;
you tell MySQL what encoding to use in your tables; MySQL converts
as needed when INSERTing / SELECTing.
You tell MySQL about your application bytes by using SET NAMES
or the underlying variables. You tell MySQL about the table bytes
in CREATE TABLE.
Actually, the charset and collation of a column has a long, not too consistent,
defaulting mechanism:
⚈ Default for system
⚈ Default for a database ...
⚈ Default for a table
⚈ Settings for a column
⚈ Fields/literals can be converted on the fly
⚈ A different collation can be specified in a WHERE clause...
To see what charset & collation are in effect for a column, use SHOW CREATE TABLE.
Life Cycle of a Character
Data -> Your client application -> INSERT into MySQL table. Simple? No.
You get the data from somewhere. Is the e-acute in "Beyoncé" represented
in one latin1 byte, hex E9? Or is it the two-byte utf8 C3 A9?
Not knowing which you have is the first step toward having a mess
in the database. More in a minute.
Meanwhile, you have declared a column in a table in a database in MySQL
to be utf8. (Collation is not matter in this discussion.)
Now you INSERT INTO Tbl (name) VALUES ("Beyoncé").
And the mess continues.
What that value being inserted encoded in latin1 or utf8?
MySQL is happy to take either, and will gladly do any transcoding
to store it properly in the table. But you have to tell it!
The default may not be right!
Your application announces to MySQL the encoding via any of
⚈ The VARIABLE character_set_client
⚈ Executing SET NAMES
⚈ init-connect in my.cnf (my.ini) (Caution: root bypasses this)
I recommend you Execute SET NAMES in your application, right after
connecting to the database. This reminds you, in your code, that
you are establising for the connection.
Example of a mess:
The data is in utf8, the connection settings are in latin1.
The table is in utf8. So, MySQL converts each of C3 and A9
(which it was told was latin1) to utf8, leading to 4 bytes
in the table: C3 83 C2 A9. I call this double-encoding.
The problem was caused by lying (usually accidentally)
to MySQL about what encoding was in the application's data.
Another example:
The data was loaded in an old system in the following manner.
The application got utf8 bytes and blindly stored them in a latin1
column (using the default latin1 settings for character_set% --
or it was done in 4.0, which had no character set concept).
This left e-acute as 2 bytes in the table in a latin1 column.
Then, you decide to 'do the right thing' and switch to utf8.
Now what?
BIG5 example:
You have an application that lets the user enter data into a form.
This data is stored into a VARCHAR. All along the way, you use
latin1. But the user connected from China with his browser set
to BIG5. The latin1 column happily accepted the 2-byte Chinese characters.
But your application reading the data has no clue of actual encoding.
That same user, connecting in that same way, and rereading his
data, will probably see the latin1 treated as BIG5, and be oblivious
of the "two wrongs make a right" that happened.
But display that text elsewhere, and you have a mess.
Disaster before INSERT
Most Mojibake starts even before the text is in the table.
Let's dissect the steps of doing an INSERT.
Step 1 -- Get the data.
First you get the data from somewhere
⚈ Type it in, using a special keyboard, or keyboard tricks
⚈ Load it from a file
⚈ Create it from constants in your client programming language
⚈ Receive text from an html form
What do you have? You probably don't know. There is no single standard for what
bytes are generated.
Step 2 -- INSERT
Let's walk through inserting a single character: e-acute.
INSERT INTO tbl VALUES ('é');
or
$str = ...; INSERT INTO tbl VALUES ('$str');
Well, this is really two steps.
Two likely cases for the encoding of e-acute:
⚈ latin1 (or code pages 1280, 1282): hex E9
⚈ utf8: 2 hex bytes: C3A9
Step 2a -- Send the command to the MySQL server
The bytes that make up the INSERT statement are sent from the client.
Here enters the VARIABLE character_set_client. This tells the client
what the bytes mean.
What is logically sent across the wire:
Client byte(s)
character_set_client: E9 C3A9
latin1 é Ã ©
utf8 é
⚈ Upper-left and lower-right: The correct 'character' is sent.
⚈ Upper-right: Since you said the bytes are 'latin1', then C3A9 must be the two chars é.
⚈ Lower-left: Since E9 (and what follows) is not valid utf8, the string is truncated.
Step 2b -- Do the INSERT
The 'characters' on the wire are now transliterated to the desired encoding for the table.
On the wire
column's charset: é Ã ©
latin1 E9 C3A9
utf8 C3A9 C383C2A9
⚈ Left column: all is well.
⚈ Upper-right: Mojibake. Treated as latin1, C3A9 looks like é
⚈ Lower-right: "Double encoding" -- discussed at length below.
See also:
Mojibake
Thread with data entry issues.
Graphemica
Diagnosing CHARSET issues
You have some non-English characters that are showing up strangely,
perhaps as a question mark (?), perhaps as two letters, when you
were expecting one, such as ç is displayed as ç.
First, let's get the lay of the land:
SHOW SESSION VARIABLES LIKE 'character_set%';
| character_set_client | latin1
| character_set_connection | latin1
| character_set_results | latin1
(There will be other rows; these three are the important ones.)
SET NAMES sets those three; this example shows the result of
SET NAMES latin1, not the recommended setting.
More later.
SHOW CREATE TABLE tbl
will show the charset and collation for each column.
More later.
You have data in a table, but it is garbled when displaying it.
The first thing to do is to decide whether the data was stored correctly.
Find a small cell that is bad, and do this:
SELECT x, HEX(x), LENGTH(x), CHAR_LENGTH(x) FROM ... WHERE ... LIMIT 1
You will get 4 columns:
⚈ x -- probably not useful, but may help confirm that you got the cell you wanted.
⚈ HEX(x) -- This lets us look at the encoding; more later.
⚈ LENGTH is the number of _bytes_.
⚈ CHAR_LENGTH is the number of _characters_. (Remember the distinction, above?)
LENGTH vs CHAR_LENGTH -- Are they equal? For correctly stored utf8,
they should be not equal. Let's look at the two lengths of specific types of characters:
Looking at a single utf8 character, would see:
⚈ ASCII: 1:1
⚈ LATIN1: 1:1
⚈ English in utf8: 1:1 -- Ascii is a subset of utf8
⚈ Western European accented letters: 2:1 -- one character is encoded in two bytes
⚈ Eastern Europe: also 2:1 (mostly)
⚈ East Asia: mostly 3:1
⚈ "double encoding" 4:1 (Europe) or 6:1 (Asian)
⚈ With utf8mb4, a few Chinese characters will be 4:1
A typical phrase in French might have LENGTH=20 and CHAR_LENGTH=17,
implying that there were 17 characters, 3 of which had accents.
latin1 is happy to cram Western European characters into 1 byte.
"Double encoding" is a term I made up for the following situation.
It is a case where "two wrongs make a right".
⚈ Table defined utf8
⚈ INSERTer declared latin1 (used default instead of doing SET NAMES)
⚈ Data being INSERTed is actually utf8 already.
What happened:
⚈ A 2-byte letter (say, a grave-e) was correctly represented in utf8.
⚈ The INSERT statement handed the 2 bytes to MySQL, but implied the need for conversion
⚈ Each byte was converted to utf8
⚈ The table has 4 bytes.
When SELECTing, the reverse happens 4->2->1, and the user
is oblivious of the bug. Looking CHAR_LENGTH will spot it.
Strange orderings may happen.
details of a double encoding
A good writeup on double-encoding
If you are moving the data using mysqldump, consider
looking at the dump file via some hexdump utility.
For experimenting, mysqldump lets you specify one database,
one table, and constrain the rows via --where="...".
In the hexdump, you need to be able to recognize various byte patterns:
In this, "yy" stands for the range 80-BF.
⚈ 09,0A,0D -- The only control character you are likely to encounter (TAB, NL, CR)
⚈ 20 -- space (handy to know when reading hex dump)
⚈ 21-7E -- Ascii characters
⚈ C2yy -- symbols
⚈ C3yy -- Typical accented letters of Western Europe
⚈ Cxyy -- More Western Europe: Latin (C3-CA), Combining Diacritical Marks (CC-CD), Greek (CE-CF)
⚈ Dxyy -- utf8: Cyrillic (D0-D4), Hebrew (D6-D7), Arabic/Persian/Farsi (D8-DB), etc
⚈ E0yyyy -- 3-byte utf8 for various Indian character sets, etc.
⚈ E1yyyy -- Hangul, Cherokee, Balinese, etc.
⚈ E2yyyy -- Symbols, Braille, etc
⚈ E381yy -- Hiragana (Japanese)
⚈ E383yy -- Katakana (Japanese)
⚈ Exyyyy -- (E3-ED) Chinese, Japanese, Korean?
⚈ EFBxyy -- Arabic
⚈ F0yyyyyy -- 4-byte utf8. (See notes about utf8mb4)
Note, especially, the absence of 80-BF unless preceeded by some other 8-bit code.
If the data is not utf8, you may see
⚈ 00xx -- Lots of these pairs could mean Ascii in UCS2. (Shift-JIS is also possible)
⚈ FFFE (or FEFF) at start of file -- utf8 file (not cell in MySQL table)
⚈ 00-1F -- control characters (mostly 0a, newline)
⚈ 20 -- space (handy to know when reading hex dump)
⚈ 21-7E -- Ascii characters
⚈ 7F -- DEL; rare
⚈ 80-FF -- "8-bit characters":
⚈ 80-9F -- Possibly MicroSoft Word things (smart quotes, etc)
⚈ 80-FF -- valid latin1 encodings, mostly European accented letters
⚈ A0 -- "hard space" -- mostly from MicroSoft
⚈ Cx not followed by yy (80-BF) cannot be utf8. This is usually a quick clue that it is not utf8.
Note: The Microsoft things really need to be fixed _before_ trying
to store into a table.
MicroSoft characters
In hex, "double encoding" will have a lot of "Cx yy Cx yy".
The manual
charset-connection.html
is that manual page that explains the interactions of the vital variables:
⚈ character_set_client -- the encoding in the client.
⚈ character_set_connection -- what to re-code characters into for client-to-server transfer
⚈ character_set_results -- what to re-code characters into for server-to-client transfer
The page also discusses collation_connection and collation_database, which have less impact.
Between that manual page and this document, I hope that you can
understand things as they relate to your situation.
To see your current settings:
SHOW VARIABLES LIKE 'character_set%'; SHOW VARIABLES LIKE 'collation%';
Desired string: სახლი არის Hex in table: C3A1C692C2A1C3A1C692C290C3A1C692C2AEC3A1C692C5A1C3A1C692CB9C20C3A1C692C290C3A1C692C2A0C3A1C692CB9CC3A1C692C2A1 Correct utf8 hex: E183A1E18390E183AEE1839AE1839820E18390E183A0E18398E183A1
C3A1C692C2A1 -- should be E183A1 -- Georgian SAN (U+10E1) "ს" C3A1C692C290 C3A1C692C2AE C3A1C692C5A1 C3A1C692CB9C 20 -- space (same in utf8 and latin1) C3A1C692C290 C3A1C692C2A0 C3A1C692CB9C C3A1C692C2A1Georgian, when properly encoded in utf8, should be 3 bytes per character:
E182xx E183xx E2B4xxTaking the first "character" (C3A1C692C2A1, in hex):
SELECT
HEX(CONVERT(CONVERT(UNHEX(
'C3A1C692C2A1C3A1C692C290C3A1C692C2AEC3A1C692C5A1C3A1C692CB9C20C3A1C692C290C3A1C692C2A0C3A1C692CB9CC3A1C692C2A1'
) USING utf8) USING latin1))
-->
E183A1E18390E183AEE1839AE1839820E18390E183A0E18398E183A1
Now to display as utf8:
SELECT UNHEX('E183A1E18390E183AEE1839AE1839820E18390E183A0E18398E183A1')
-->
სახლი არის
SELECT HEX(CONVERT(CONVERT(UNHEX( 'C398C2AF') USING utf8) USING latin1)); --> D8AF, which is the utf8 for Arabic DAL. SELECT CONVERT(CONVERT(UNHEX( 'C398C2AF') USING utf8) USING latin1); --> that character.(Ref: forum thread #565669.)
# Set up bad case:
mysql> create table latin1 (c varchar(11) charset latin1);
mysql> insert into latin1 (c) values ('x'), (unhex('c2a3'));
mysql> select c, hex(c), length(c), char_length(c) from latin1;
+------+--------+-----------+----------------+
| c | hex(c) | length(c) | char_length(c) |
+------+--------+-----------+----------------+
| x | 78 | 1 | 1 |
| A£ | C2A3 | 2 | 2 |
+------+--------+-----------+----------------+
# Step 1 of 2 of fix:
mysql> alter table latin1 modify c tinyblob;
mysql> select c, hex(c), length(c), char_length(c) from latin1;
+------+--------+-----------+----------------+
| c | hex(c) | length(c) | char_length(c) |
+------+--------+-----------+----------------+
| x | 78 | 1 | 1 |
| A£ | C2A3 | 2 | 2 |
+------+--------+-----------+----------------+
# Step 2 of 2 of fix:
mysql> alter table latin1 modify c varchar(11) charset utf8;
mysql> select c, hex(c), length(c), char_length(c) from latin1;
+------+--------+-----------+----------------+
| c | hex(c) | length(c) | char_length(c) |
+------+--------+-----------+----------------+
| x | 78 | 1 | 1 |
| £ | C2A3 | 2 | 1 |
+------+--------+-----------+----------------+
UPDATE Tbl SET col =
IFNULL(CONVERT(CONVERT(CONVERT(col USING latin1)
USING binary)
USING utf8),
col )
Caution: I have only briefly tested this.
mysql> # Create sample:
mysql> CREATE TABLE dbl (c VARCHAR(11) CHARSET latin1);
mysql> INSERT INTO dbl (c) VALUES ('x'), (UNHEX('C382C2A4'));
mysql> # Diagnose:
mysql> SELECT c, HEX(c), LENGTH(c), CHAR_LENGTH(c) FROM dbl;
+------+----------+-----------+----------------+
| c | HEX(c) | LENGTH(c) | CHAR_LENGTH(c) |
+------+----------+-----------+----------------+
| x | 78 | 1 | 1 |
| A,A☼ | C382C2A4 | 4 | 4 |
+------+----------+-----------+----------------+
mysql> # Step 1 of 5:
Note: Steps 1 and 2 are needed only if you start with latin1;
If your column is already utf8, skip to step 3.
mysql> ALTER TABLE dbl MODIFY c TINYBLOB;
mysql> SELECT c, HEX(c), LENGTH(c), CHAR_LENGTH(c) FROM dbl;
+------+----------+-----------+----------------+
| c | HEX(c) | LENGTH(c) | CHAR_LENGTH(c) |
+------+----------+-----------+----------------+
| x | 78 | 1 | 1 |
| A,A☼ | C382C2A4 | 4 | 4 |
+------+----------+-----------+----------------+
mysql> # Step 2 of 5:
mysql> ALTER TABLE dbl MODIFY c VARCHAR(11) CHARSET utf8;
mysql> SELECT c, HEX(c), LENGTH(c), CHAR_LENGTH(c) FROM dbl;
+------+----------+-----------+----------------+
| c | HEX(c) | LENGTH(c) | CHAR_LENGTH(c) |
+------+----------+-----------+----------------+
| x | 78 | 1 | 1 |
| A☼ | C382C2A4 | 4 | 2 |
+------+----------+-----------+----------------+
mysql> # Step 3 of 5:
mysql> ALTER TABLE dbl MODIFY c VARCHAR(11) CHARSET latin1;
mysql> SELECT c, HEX(c), LENGTH(c), CHAR_LENGTH(c) FROM dbl;
+------+--------+-----------+----------------+
| c | HEX(c) | LENGTH(c) | CHAR_LENGTH(c) |
+------+--------+-----------+----------------+
| x | 78 | 1 | 1 |
| A☼ | C2A4 | 2 | 2 |
+------+--------+-----------+----------------+
mysql> # Step 4 of 5:
mysql> ALTER TABLE dbl MODIFY c TINYBLOB;
mysql> SELECT c, HEX(c), LENGTH(c), CHAR_LENGTH(c) FROM dbl;
+------+--------+-----------+----------------+
| c | HEX(c) | LENGTH(c) | CHAR_LENGTH(c) |
+------+--------+-----------+----------------+
| x | 78 | 1 | 1 |
| A☼ | C2A4 | 2 | 2 |
+------+--------+-----------+----------------+
mysql> # Step 5 of 5:
mysql> ALTER TABLE dbl MODIFY c VARCHAR(11) CHARSET utf8;
mysql> SELECT c, HEX(c), LENGTH(c), CHAR_LENGTH(c) FROM dbl;
+------+--------+-----------+----------------+
| c | HEX(c) | LENGTH(c) | CHAR_LENGTH(c) |
+------+--------+-----------+----------------+
| x | 78 | 1 | 1 |
| ☼ | C2A4 | 2 | 1 |
+------+--------+-----------+----------------+
mysql> # Check schema:
mysql> SHOW CREATE TABLE dbl \G
CREATE TABLE `dbl` (
`c` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1. mysqldump --default-character-set=latin1 .... my_database > my_database_latin1.sql
2. Edit my_database_latin1.sql to set NAMES=utf8 at the top.
3. mysql ... < mydatabase.sqlmo
Thanks to Peter Barry
mysql> # Create sample:
mysql> CREATE TABLE trycvt (c VARCHAR(11) CHARSET latin1);
mysql> INSERT INTO trycvt (c) VALUES ('x'), (UNHEX('BB'));
mysql> SHOW CREATE TABLE trycvt \G
CREATE TABLE `trycvt` (
`c` varchar(11) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> # Diagnose:
mysql> SELECT c, HEX(c), LENGTH(c), CHAR_LENGTH(c) FROM trycvt;
+------+--------+-----------+----------------+
| c | HEX(c) | LENGTH(c) | CHAR_LENGTH(c) |
+------+--------+-----------+----------------+
| x | 78 | 1 | 1 |
| » | BB | 1 | 1 |
+------+--------+-----------+----------------+
mysql> # Step 1 of 1
mysql> ALTER TABLE trycvt CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
mysql> # Diagnose:
mysql> SELECT c, HEX(c), LENGTH(c), CHAR_LENGTH(c) FROM trycvt;
+------+--------+-----------+----------------+
| c | HEX(c) | LENGTH(c) | CHAR_LENGTH(c) |
+------+--------+-----------+----------------+
| x | 78 | 1 | 1 |
| » | C2BB | 2 | 1 |
+------+--------+-----------+----------------+
mysql> # Note schema changed:
mysql> SHOW CREATE TABLE trycvt \G
CREATE TABLE `trycvt` (
`c` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
mysqldump -h old_host --no-data >schema.sql
# now edit schema.sql to fix charset, collation, etc
# edit in/out SET NAMES
mysql -h new_host <schema.sql
mysqldump -h old_host --no-create-info --opt >data.sql
# edit in/out SET NAMES
mysql -h new_host <data.sql
unicode: 005E = (^) utf8: 5E = CIRCUMFLEX ACCENT = SPACING CIRCUMFLEX unicode: 02C6 = (ˆ) utf8: CB86 = MODIFIER LETTER CIRCUMFLEX ACCENT = MODIFIER LETTER CIRCUMFLEX unicode: 0302 = utf8: CC82 = COMBINING CIRCUMFLEX ACCENT = NON-SPACING CIRCUMFLEX unicode: 032D = utf8: CCAD = COMBINING CIRCUMFLEX ACCENT BELOW = NON-SPACING CIRCUMFLEX BELOW unicode: 1DCD = utf8: E1B78D = COMBINING DOUBLE CIRCUMFLEX ABOVE unicode: A788 = (ꞈ) utf8: EA9E88 = MODIFIER LETTER LOW CIRCUMFLEX ACCENT unicode: FF3E = (＾) utf8: EFBCBE = FULLWIDTH CIRCUMFLEX ACCENT = FULLWIDTH SPACING CIRCUMFLEX unicode: E005E = utf8: F3A0819E = TAG CIRCUMFLEX ACCENTThis debates what latin1's 0x88 (ˆ) maps to: forum discussion
SHOW CHARSET; -- only latin1 and utf8 are likely to be interesting
SHOW COLLATION; -- list the possible values.
SHOW COLLATION LIKE 'utf8%';
SHOW COLLATION LIKE '%_cs';
SHOW SESSION VARIABLES LIKE 'character_set%';
SHOW GLOBAL VARIABLES LIKE 'character_set%';
SHOW CREATE TABLE tbl \G
[client]
default-character-set = cp1252
[mysqld]
skip-character-set-client-handshake
default-character-set = cp1252
character-set-server = cp1252
collation-server = latin1_general_cs
init-connect = SET NAMES cp1252
[mysqldump]
default-character-set = cp1252
[mysql]
default-character-set = cp1252
utf8 : utf8_general_ci A=a=À=Á=Â=Ã=Ä=Å=à=á=â=ã=ä=å Aa ae az utf8 : utf8_general_mysql500_ci A=a=À=Á=Â=Ã=Ä=Å=à=á=â=ã=ä=å Aa ae az utf8 : utf8_danish_ci A=a=ª=À=Á=Â=Ã=à=á=â=ã ae az utf8 : utf8_swedish_ci A=a=ª=À=Á=Â=Ã=à=á=â=ã Aa ae az utf8 : utf8_estonian_ci A=a=ª=À=Á=Â=Ã=Å=à=á=â=ã=å Aa ae az Æ=æ utf8 : utf8_unicode_ci A=a=ª=À=Á=Â=Ã=Ä=Å=à=á=â=ã=ä=å Aa ae az Æ=æ utf8 : utf8_polish_ci A=a=ª=À=Á=Â=Ã=Ä=Å=à=á=â=ã=ä=å Aa ae az Æ=æ utf8 : utf8_spanish_ci A=a=ª=À=Á=Â=Ã=Ä=Å=à=á=â=ã=ä=å Aa ae az Æ=æ utf8 : utf8_spanish2_ci A=a=ª=À=Á=Â=Ã=Ä=Å=à=á=â=ã=ä=å Aa ae az Æ=æ utf8 : utf8_general_ci B=b C=c=Ç=ç ch cz D=d dz utf8 : utf8_general_mysql500_ci B=b C=c=Ç=ç ch cz D=d dz utf8 : utf8_danish_ci B=b C=c=Ç=ç ch cz D=d dz Ð=ð utf8 : utf8_swedish_ci B=b C=c=Ç=ç ch cz D=d dz Ð=ð utf8 : utf8_estonian_ci B=b C=c=Ç=ç ch cz D=d dz Ð=ð utf8 : utf8_unicode_ci B=b C=c=Ç=ç ch cz D=d dz Ð=ð utf8 : utf8_polish_ci B=b C=c=Ç=ç ch cz D=d dz Ð=ð utf8 : utf8_spanish_ci B=b C=c=Ç=ç ch cz D=d dz Ð=ð utf8 : utf8_spanish2_ci B=b C=c=Ç=ç cz ch D=d dz Ð=ð utf8 : utf8_general_ci E=e=È=É=Ê=Ë=è=é=ê=ë=ĕ=Ė=ė=Ę=ę=Ě=ě utf8 : utf8_general_mysql500_ci E=e=È=É=Ê=Ë=è=é=ê=ë=ĕ=Ė=ė=Ę=ę=Ě=ě utf8 : utf8_danish_ci E=e=È=É=Ê=Ë=è=é=ê=ë=ĕ=Ė=ė=Ę=ę=Ě=ě utf8 : utf8_swedish_ci E=e=È=É=Ê=Ë=è=é=ê=ë=ĕ=Ė=ė=Ę=ę=Ě=ě utf8 : utf8_estonian_ci E=e=È=É=Ê=Ë=è=é=ê=ë=ĕ=Ė=ė=Ę=ę=Ě=ě utf8 : utf8_unicode_ci E=e=È=É=Ê=Ë=è=é=ê=ë=ĕ=Ė=ė=Ę=ę=Ě=ě utf8 : utf8_polish_ci E=e=È=É=Ê=Ë=è=é=ê=ë=ĕ=Ė=ė=Ě=ě Ę=ę utf8 : utf8_spanish_ci E=e=È=É=Ê=Ë=è=é=ê=ë=ĕ=Ė=ė=Ę=ę=Ě=ě utf8 : utf8_spanish2_ci E=e=È=É=Ê=Ë=è=é=ê=ë=ĕ=Ė=ė=Ę=ę=Ě=ě utf8 : utf8_general_ci F=f fz G=g H=h hz utf8 : utf8_general_mysql500_ci F=f fz G=g H=h hz utf8 : utf8_danish_ci F=f fz ƒ G=g H=h hz utf8 : utf8_swedish_ci F=f fz ƒ G=g H=h hz utf8 : utf8_estonian_ci F=f fz ƒ G=g H=h hz utf8 : utf8_unicode_ci F=f fz ƒ G=g H=h hz utf8 : utf8_polish_ci F=f fz ƒ G=g H=h hz utf8 : utf8_spanish_ci F=f fz ƒ G=g H=h hz utf8 : utf8_spanish2_ci F=f fz ƒ G=g H=h hz utf8 : utf8_general_ci I=i=Ì=Í=Î=Ï=ì=í=î=ï ij iz utf8 : utf8_general_mysql500_ci I=i=Ì=Í=Î=Ï=ì=í=î=ï ij iz utf8 : utf8_danish_ci I=i=Ì=Í=Î=Ï=ì=í=î=ï ij=ij iz utf8 : utf8_swedish_ci I=i=Ì=Í=Î=Ï=ì=í=î=ï ij=ij iz utf8 : utf8_estonian_ci I=i=Ì=Í=Î=Ï=ì=í=î=ï ij=ij iz utf8 : utf8_unicode_ci I=i=Ì=Í=Î=Ï=ì=í=î=ï ij=ij iz utf8 : utf8_polish_ci I=i=Ì=Í=Î=Ï=ì=í=î=ï ij=ij iz utf8 : utf8_spanish_ci I=i=Ì=Í=Î=Ï=ì=í=î=ï ij=ij iz utf8 : utf8_spanish2_ci I=i=Ì=Í=Î=Ï=ì=í=î=ï ij=ij iz utf8 : utf8_general_ci J=j K=k L=l lj ll lz M=m N=n=Ñ=ñ nz utf8 : utf8_general_mysql500_ci J=j K=k L=l lj ll lz M=m N=n=Ñ=ñ nz utf8 : utf8_danish_ci J=j K=k L=l lj=LJ=Lj=lj ll lz Ł=ł M=m N=n=Ñ=ñ nz utf8 : utf8_swedish_ci J=j K=k L=l lj=LJ=Lj=lj ll lz Ł=ł M=m N=n=Ñ=ñ nz utf8 : utf8_estonian_ci J=j K=k L=l lj=LJ=Lj=lj ll lz Ł=ł M=m N=n=Ñ=ñ nz utf8 : utf8_unicode_ci J=j K=k L=l lj=LJ=Lj=lj ll lz Ł=ł M=m N=n=Ñ=ñ nz utf8 : utf8_polish_ci J=j K=k L=l lj=LJ=Lj=lj ll lz Ł=ł M=m N=n=Ñ=ñ nz utf8 : utf8_spanish_ci J=j K=k L=l lj=LJ=Lj=lj ll lz Ł=ł M=m N=n nz Ñ=ñ utf8 : utf8_spanish2_ci J=j K=k L=l lj=LJ=Lj=lj lz ll Ł=ł M=m N=n nz Ñ=ñ utf8 : utf8_general_ci O=o=Ò=Ó=Ô=Õ=Ö=ò=ó=ô=õ=ö oe oz utf8 : utf8_general_mysql500_ci O=o=Ò=Ó=Ô=Õ=Ö=ò=ó=ô=õ=ö oe oz utf8 : utf8_danish_ci O=o=º=Ò=Ó=Ô=Õ=ò=ó=ô=õ oe=Œ=œ oz utf8 : utf8_swedish_ci O=o=º=Ò=Ó=Ô=Õ=ò=ó=ô=õ oe=Œ=œ oz utf8 : utf8_estonian_ci O=o=º=Ò=Ó=Ô=ò=ó=ô oe=Œ=œ oz Ø=ø utf8 : utf8_unicode_ci O=o=º=Ò=Ó=Ô=Õ=Ö=ò=ó=ô=õ=ö oe=Œ=œ oz Ø=ø utf8 : utf8_polish_ci O=o=º=Ò=Ô=Õ=Ö=ò=ô=õ=ö oe=Œ=œ oz Ó=ó Ø=ø utf8 : utf8_spanish_ci O=o=º=Ò=Ó=Ô=Õ=Ö=ò=ó=ô=õ=ö oe=Œ=œ oz Ø=ø utf8 : utf8_spanish2_ci O=o=º=Ò=Ó=Ô=Õ=Ö=ò=ó=ô=õ=ö oe=Œ=œ oz Ø=ø utf8 : utf8_general_ci P=p Q=q R=r S=s=ß=Š=š sh ss sz T=t TM=tm tz utf8 : utf8_general_mysql500_ci P=p Q=q R=r S=s=Š=š sh ss sz T=t TM=tm tz utf8 : utf8_danish_ci P=p Q=q R=r S=s=Š=š sh ss=ß sz T=t TM=tm=™ tz utf8 : utf8_swedish_ci P=p Q=q R=r S=s=Š=š sh ss=ß sz T=t TM=tm=™ tz utf8 : utf8_estonian_ci P=p Q=q R=r S=s sh ss=ß sz Š=š Z=z zh zz Ž=ž T=t TM=tm=™ tz utf8 : utf8_unicode_ci P=p Q=q R=r S=s=Š=š sh ss=ß sz T=t TM=tm=™ tz utf8 : utf8_polish_ci P=p Q=q R=r S=s=Š=š sh ss=ß sz T=t TM=tm=™ tz utf8 : utf8_spanish_ci P=p Q=q R=r S=s=Š=š sh ss=ß sz T=t TM=tm=™ tz utf8 : utf8_spanish2_ci P=p Q=q R=r S=s=Š=š sh ss=ß sz T=t TM=tm=™ tz utf8 : utf8_general_ci U=u=Ù=Ú=Û=Ü=ù=ú=û=ü ue uz utf8 : utf8_general_mysql500_ci U=u=Ù=Ú=Û=Ü=ù=ú=û=ü ue uz utf8 : utf8_danish_ci U=u=Ù=Ú=Û=ù=ú=û ue uz utf8 : utf8_swedish_ci U=u=Ù=Ú=Û=ù=ú=û ue uz utf8 : utf8_estonian_ci U=u=Ù=Ú=Û=ù=ú=û ue uz utf8 : utf8_unicode_ci U=u=Ù=Ú=Û=Ü=ù=ú=û=ü ue uz utf8 : utf8_polish_ci U=u=Ù=Ú=Û=Ü=ù=ú=û=ü ue uz utf8 : utf8_spanish_ci U=u=Ù=Ú=Û=Ü=ù=ú=û=ü ue uz utf8 : utf8_spanish2_ci U=u=Ù=Ú=Û=Ü=ù=ú=û=ü ue uz utf8 : utf8_general_ci V=v W=w utf8 : utf8_general_mysql500_ci V=v W=w utf8 : utf8_danish_ci V=v W=w utf8 : utf8_swedish_ci V=v W=w utf8 : utf8_estonian_ci V=v W=w Õ=õ Ä=ä Ö=ö Ü=ü utf8 : utf8_unicode_ci V=v W=w utf8 : utf8_polish_ci V=v W=w utf8 : utf8_spanish_ci V=v W=w utf8 : utf8_spanish2_ci V=v W=w utf8 : utf8_general_ci X=x Y=y=Ý=ý=ÿ=Ÿ yz Z=z=Ž=ž zh zz Æ=æ Ð=ð Ø=ø Þ=þ Ł=ł Œ=œ ƒ LJ=Lj=lj utf8 : utf8_general_mysql500_ci X=x Y=y=Ý=ý=ÿ=Ÿ yz Z=z=Ž=ž zh zz Æ=æ Ð=ð Ø=ø Þ=þ ß Ł=ł Œ=œ ƒ LJ=Lj=lj utf8 : utf8_danish_ci X=x Y=y=Ü=Ý=ü=ý=ÿ=Ÿ yz Z=z=Ž=ž zh zz Ä=Æ=ä=æ Ö=Ø=ö=ø Aa=Å=å Þ=þ utf8 : utf8_swedish_ci X=x Y=y=Ü=Ý=ü=ý=ÿ=Ÿ yz Z=z=Ž=ž zh zz Å=å Ä=Æ=ä=æ Ö=Ø=ö=ø Þ=þ utf8 : utf8_estonian_ci X=x Y=y=Ý=ý=ÿ=Ÿ yz Þ=þ utf8 : utf8_unicode_ci X=x Y=y=Ý=ý=ÿ=Ÿ yz Z=z=Ž=ž zh zz Þ=þ utf8 : utf8_polish_ci X=x Y=y=Ý=ý=ÿ=Ÿ yz Z=z=Ž=ž zh zz Þ=þ utf8 : utf8_spanish_ci X=x Y=y=Ý=ý=ÿ=Ÿ yz Z=z=Ž=ž zh zz Þ=þ utf8 : utf8_spanish2_ci X=x Y=y=Ý=ý=ÿ=Ÿ yz Z=z=Ž=ž zh zz Þ=þutf8mb4_% mostly works the same as utf8.
128 80 E282AC € € 129 81 C281  130 82 E2809A ‚ ‚ 131 83 C692 ƒ ƒ 132 84 E2809E „ „ 133 85 E280A6 … … 134 86 E280A0 † † 135 87 E280A1 ‡ ‡ 136 88 CB86 ˆ ˆ 137 89 E280B0 ‰ ‰ 138 8A C5A0 Š Š 139 8B E280B9 ‹ ‹ 140 8C C592 Œ Œ 141 8D C28D  142 8E C5BD Ž Ž 143 8F C28F  144 90 C290  145 91 E28098 ‘ ‘ 146 92 E28099 ’ ’ 147 93 E2809C “ “ 148 94 E2809D ” ” 149 95 E280A2 • • 150 96 E28093 – – 151 97 E28094 — — 152 98 CB9C ˜ ˜ 153 99 E284A2 ™ ™ 154 9A C5A1 š š 155 9B E280BA › › 156 9C C593 œ œ 157 9D C29D  158 9E C5BE ž ž 159 9F C5B8 Ÿ Ÿ |
160 A0 C2A0 161 A1 C2A1 ¡ ¡ 162 A2 C2A2 ¢ ¢ 163 A3 C2A3 £ £ 164 A4 C2A4 ¤ ¤ 165 A5 C2A5 ¥ ¥ 166 A6 C2A6 ¦ ¦ 167 A7 C2A7 § § 168 A8 C2A8 ¨ ¨ 169 A9 C2A9 © © 170 AA C2AA ª ª 171 AB C2AB « « 172 AC C2AC ¬ ¬ 173 AD C2AD ­ 174 AE C2AE ® ® 175 AF C2AF ¯ ¯ 176 B0 C2B0 ° ° 177 B1 C2B1 ± ± 178 B2 C2B2 ² ² 179 B3 C2B3 ³ ³ 180 B4 C2B4 ´ ´ 181 B5 C2B5 µ µ 182 B6 C2B6 ¶ ¶ 183 B7 C2B7 · · 184 B8 C2B8 ¸ ¸ 185 B9 C2B9 ¹ ¹ 186 BA C2BA º º 187 BB C2BB » » 188 BC C2BC ¼ ¼ 189 BD C2BD ½ ½ 190 BE C2BE ¾ ¾ 191 BF C2BF ¿ ¿ |
192 C0 C380 À À 193 C1 C381 Á Á 194 C2 C382 Â Â 195 C3 C383 Ã Ã 196 C4 C384 Ä Ä 197 C5 C385 Å Å 198 C6 C386 Æ Æ 199 C7 C387 Ç Ç 200 C8 C388 È È 201 C9 C389 É É 202 CA C38A Ê Ê 203 CB C38B Ë Ë 204 CC C38C Ì Ì 205 CD C38D Í Í 206 CE C38E Î Î 207 CF C38F Ï Ï 208 D0 C390 Ð Ð 209 D1 C391 Ñ Ñ 210 D2 C392 Ò Ò 211 D3 C393 Ó Ó 212 D4 C394 Ô Ô 213 D5 C395 Õ Õ 214 D6 C396 Ö Ö 215 D7 C397 × × 216 D8 C398 Ø Ø 217 D9 C399 Ù Ù 218 DA C39A Ú Ú 219 DB C39B Û Û 220 DC C39C Ü Ü 221 DD C39D Ý Ý 222 DE C39E Þ Þ 223 DF C39F ß ß |
224 E0 C3A0 à à 225 E1 C3A1 á á 226 E2 C3A2 â â 227 E3 C3A3 ã ã 228 E4 C3A4 ä ä 229 E5 C3A5 å å 230 E6 C3A6 æ æ 231 E7 C3A7 ç ç 232 E8 C3A8 è è 233 E9 C3A9 é é 234 EA C3AA ê ê 235 EB C3AB ë ë 236 EC C3AC ì ì 237 ED C3AD í í 238 EE C3AE î î 239 EF C3AF ï ï 240 F0 C3B0 ð ð 241 F1 C3B1 ñ ñ 242 F2 C3B2 ò ò 243 F3 C3B3 ó ó 244 F4 C3B4 ô ô 245 F5 C3B5 õ õ 246 F6 C3B6 ö ö 247 F7 C3B7 ÷ ÷ 248 F8 C3B8 ø ø 249 F9 C3B9 ù ù 250 FA C3BA ú ú 251 FB C3BB û û 252 FC C3BC ü ü 253 FD C3BD ý ý 254 FE C3BE þ þ 255 FF C3BF ÿ ÿ |
C387 0199 00C7 Ç latin capital letter c with cedilla C396 0214 00D6 Ö latin capital letter o with diaeresis C39C 0220 00DC Ü latin capital letter u with diaeresis C3A7 0231 00E7 ç latin small letter c with cedilla C3B6 0246 00F6 ö latin small letter o with diaeresis C3BC 0252 00FC ü latin small letter u with diaeresis C49E 0286 011E Ğ latin capital letter g with breve C49F 0287 011F ğ latin small letter g with breve C4B0 0304 0130 İ latin capital letter i with dot above C4B1 0305 0131 ı latin small letter dotless i C59E 0350 015E Ş latin capital letter s with cedilla C59F 0351 015F ş latin small letter s with cedilla E282A4 8356 20A4 ₤ lira sign E282BA 8378 20BA ₺ Turkish lira sign
$hex = unpack('H*', $text);
echo implode('', $hex), "
";
ALTER TABLE tbl CONVERT TO CHARACTER SET utf8;
Example from forumsCONVERT(UNHEX(HEX(CONVERT(CONVERT(col USING utf8) USING latin1))), CHAR)
Cyrillic example: title: Добро пожаловать в Диксон, hex: D094D0BED0B1D180D0BE20D0BFD0BED0B6D0B0D0BBD0BED0B2D0B0D182D18C20D0B220D094D0B8D0BAD181D0BED0BD, length: 47, char length: 25
EXPLAIN EXTENDED SELECT ...; SHOW WARNINGS;sometimes gives extra clues into what charset/collation issues are being faced during a SELECT. It will probably contain 'introducers'; example: _utf8'abc'.
# Find rows with any 8-bit chars:
SELECT * FROM tbl WHERE HEX(colname) RLIKE '^(..)*[8-F].';
# Remove all occurrences of one 8-bit code (hex 92, in this example):
UPDATE tbl SET colname = REPLACE(colname, UNHEX('92'), '');
Reference:
how to replace hex in varchar field