One of the responses to a question I asked yesterday suggested that I should make sure my database can handle UTF-8 characters correctly. How I can do this with MySQL?
Community♦
Create MySQL Database, Table & User From Command Line Guide This article describes how to do common MySQL database administration tasks from the command line using the mysql program. This article only applies to the products listed in the Article Details sidebar. To change the character set encoding to UTF-8 for the database itself, type the following command at the mysql prompt. Replace DBNAME with the database name: ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8generalci; To exit the mysql program, type q at the mysql prompt.
BenBen
14 Answers
Update:
Short answer - You should almost always be using the
utf8mb4
charset and utf8mb4_unicode_ci
collation.To alter database:
See:
- Aaron's comment on this answer How to make MySQL handle UTF-8 properlyTissue hatch cad download. Step 2:Uncompress the.zip file.
- Conversion guide: https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-conversion.html
Original Answer:
MySQL 4.1 and above has a default character set of UTF-8. You can verify this in your
my.cnf
file, remember to set both client and server (default-character-set
and character-set-server
).If you have existing data that you wish to convert to UTF-8, dump your database, and import it back as UTF-8 making sure:
- use
SET NAMES utf8
before you query/insert into the database - use
DEFAULT CHARSET=utf8
when creating new tables - at this point your MySQL client and server should be in UTF-8 (see
my.cnf
). remember any languages you use (such as PHP) must be UTF-8 as well. Some versions of PHP will use their own MySQL client library, which may not be UTF-8 aware.
If you do want to migrate existing data remember to backup first! Lots of weird choping of data can happen when things don't go as planned!
Some resources:
- complete UTF-8 migration (cdbaby.com)
- article on UTF-8 readiness of php functions (note some of this information is outdated)
OwenOwen
To make this 'permanent', in
my.cnf
:To check, go to the client and show some variables:
Verify that they're all
utf8
, except .._filesystem
, which should be binary
and .._dir
, that points somewhere in the MySQL installation.JavierJavier
MySQL 4.1 and above has a default character set that it calls
utf8
but which is actually only a subset of UTF-8 (allows only three-byte characters and smaller).Use
utf8mb4
as your charset if you want 'full' UTF-8.T.W.R. ColeT.W.R. Cole
The short answer: Use
utf8mb4
in 4 places:- The bytes in your client are utf8, not latin1/cp1251/etc.
SET NAMES utf8mb4
or something equivalent when establishing the client's connection to MySQLCHARACTER SET utf8mb4
on all tables/columns -- except columns that are strictly ascii/hex/country_code/zip_code/etc.<meta charset charset=UTF-8>
if you are outputting to HTML. (Yes the spelling is different here.)
More info ;
UTF8 all the way
UTF8 all the way
The above links provide the 'detailed canonical answer is required to address all the concerns'. -- There is a space limit on this forum.
Edit
In addition to
CHARACTER SET utf8mb4
containing 'all' the world's characters, COLLATION utf8mb4_unicode_520_ci
is arguable the 'best all-around' collation to use. (There are also Turkish, Spanish, etc, collations for those who want the nuances in those languages.)Community♦
Rick JamesRick James
The charset is a property of the database (default) and the table.You can have a look (MySQL commands):
In other words; it's quite easy to check your database charset or change it:
extraneonextraneon
To change the character set encoding to UTF-8 for the database itself, type the following command at the mysql> prompt. USE
ALTER DATABASE
. Replace DBNAME with the database name:This is a duplicate of this question How to convert an entire MySQL database characterset and collation to UTF-8?
Community♦
Nyein AungNyein Aung
I followed Javier's solution, but I added some different lines in my.cnf:
I found this idea here: http://dev.mysql.com/doc/refman/5.0/en/charset-server.html in the first/only user comment on the bottom of the page. He mentions that skip-character-set-client-handshake has some importance.
Vlad BalanVlad Balan
These tips on MySQL and UTF-8 may be helpful. Unfortunately, they don't constitute a full solution, just common gotchas.
Edward Z. YangEdward Z. Yang
Set your
database collation
to UTF-8
then apply table collation
to database default.Gaurav LadGaurav Lad
Your answer is you can configure by MySql Settings. In My Answer may be something gone out of context but this is also know is help for you.
how to configure
how to configure
Character Set
and Collation
. For applications that store data using the default MySQL character set and collation (
latin1, latin1_swedish_ci
), no special configuration should be needed. If applications require data storage using a different character set or collation, you can configure character set information several ways:- Specify character settings per database. For example, applicationsthat use one database might require
utf8
, whereas applications thatuse another database might require sjis. - Specify character settings at server startup. This causes the serverto use the given settings for all applications that do not make otherarrangements.
- Specify character settings at configuration time, if you build MySQLfrom source. This causes the server to use the given settings for allapplications, without having to specify them at server startup.
The examples shown here for your question to set utf8 character set , here also set collation for more helpful(
utf8_general_ci
collation`). Specify character settings per database
Specify character settings at server startup
Specify character settings at MySQL configuration time
Stock managementInteractive stock chainThe user can create a stock chain for any number of jobs or for all jobs simultaneously. For each job, a resulting stock is generated for the previous job step.
This allows you to keep track of which stock has been used for which job. When a cycle is recalculated, stock is generated even for jobs that do not hold or carry stock.Stock generationA history is now created during manual stock generation.
To see the values of the character set and collation system variables that apply to your connection, use these statements:
This May be lengthy answer but there is all way, you can use. Hopeful my answer is helpful for you. for more information http://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
Vipin JainVipin Jain
ClaudioClaudio
sunil subramanyasunil subramanya
finfin
Was able to find a solution. Ran the following as specified at http://technoguider.com/2015/05/utf8-set-up-in-mysql/
NishantNishant
protected by Samuel Liew♦Apr 21 '18 at 7:58
Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
Would you like to answer one of these unanswered questions instead?
Not the answer you're looking for? Browse other questions tagged mysqlutf-8 or ask your own question.
When running Oracle GoldenGate for MySQL, be aware of the following:
- Oracle GoldenGate does not support
BLOB
orTEXT
types when used as a primary key. - Oracle GoldenGate supports UTF8 and UCS2 character sets. UTF8 data is converted to UTF16 by Oracle GoldenGate before writing it to the trail.
- UTF32 is not supported by Oracle GoldenGate.
- Oracle GoldenGate supports a
TIME
type range from 00:00:00 to 23:59:59. - Oracle GoldenGate supports timestamp data from
0001/01/03:00:00:00
to9999/12/31:23:59:59
. If a timestamp is converted from GMT to local time, these limits also apply to the resulting timestamp. Depending on the time zone, conversion may add or subtract hours, which can cause the timestamp to exceed the lower or upper supported limit. - Oracle GoldenGate does not support negative dates.
- The support of range and precision for floating-point numbers depends on the host machine. In general, the precision is accurate to 16 significant digits, but you should review the database documentation to determine the expected approximations. Oracle GoldenGate rounds or truncates values that exceed the supported precision.
- When you use
ENUM
type in non-strictsql_mode
, the non-strictsql_mode
does not prevent you from entering an invalidENUM
value and an error will be returned. To avoid this situation, do one of the following:- Use
sql_mode
asSTRICT
and restart Extract. This prevents users from entering invalid values for any of the data types. An IE user can only enter valid values for those data types. - Continue using non-strict
sql_mode
, but do not useENUM
data types. - Continue using non-strict
sql_mode
and useENUM
data types with valid values in the database. If you specify invalid values, the database will silently accept them and Extract will abend.
- To preserve transaction boundaries for a MySQL target, create or alter the target tables to the InnoDB transactional database engine instead of the MyISAM engine. MyISAM will cause Replicat records to be applied as they are received, which does not guarantee transaction integrity even with auto-commit turned off. You cannot roll back a transaction with MyISAM.
- Extraction and replication from and to views is not supported.
- Transactions applied by the slave are logged into the relay logs and not into the slave's
binlog
. If you want a slave to write transactions thebinlog
that it receives from the master , you need to start the replication slave with the log slave-updates option as 1 inmy.cnf
. This is in addition to the other binary logging parameters. After the master's transactions are in the slave'sbinlog
, you can then setup a regular capture on the slave to capture and process the slave'sbinlog
.