MySQL Character set

Engine=innodb auto_increment=9 DEFAULT Charset=latinl

SOURCE Test.sql

root# mysql-uroot-p ' Oldboy < Test.sql

Default-charact-set=latinl

Not garbled ideas: Linux, clients, servers, libraries, tables, Programs!
1. Linux System
root# cat/etc/sysconfig/i18n
#LANG = "ZH_CN. GB2312 "

2. mysql Client
Temporary:
Set column name Latinl;
Method 1
#mysql-uroot-p
Set Names UTF8
Method 2
#mysql-uroot-p--default-character-set=utf8


Permanent:
Change the MY.CNF client module parameters, implement the effect of set names UTF8, and take effect permanently.
[Client]
Default-character-set=latinl
Prompt, no need to restart service, exit re-login, equivalent to set names LATINL
/etc/init.d/mysqld start

3. mysql Server
[Mysqld]
Default-character-set=utf8 version 5.1 and previous versions
Character-set-server=usf8 version 5.5

4. mysql Library table
Create DATABASE Oldboy_utf8 DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;

CREATE TABLE ' Student ' (
' ID ' int (4) not NULL auto_increment,
' Name ' char (a) is not NULL,
PRIMARY KEY (' id ')
) Engine=innodb auto_incerment=10 Dafault Charset=utf8

5.

Program
Simplified UTF8

Summarize:
Mysqldump.mysqlbinlog.mysql.mysqladmin

6. View Table structure
DESC table name;
Show columns from table name;
describe table name;
Show create table table name;

Use Information_schema
SELECT * from columns where table_name= ' table name ';
To view the table structure:
mysql> status;
Mysql> select version ();
MySQL--help | grep distrib
$ rpm-qa|grep MySQL

View table Status:
Show CREATE TABLE Course\g

To view the character set:
Mysql-uroot-p ' abc123,. '-E ' SHOW CHARACTER SET;

Mysql-uroot-p ' abc123,. '-E ' SHOW CHARACTER SET; "| Egrep "Gbk|utf8|latin1" awk ' {print $} '

{
To permanently modify the character set:
In another case, the character set can be permanently enforced, by doing the following:
For the Chinese garbled problem, you need to set up the MySQL character set:
Find the Cnf file with the Find command. Remember not to look for my.cnf because it does not necessarily exist, but should find *.cnf.
# Find/-name ' *.cnf '
/usr/share/doc/mysql-server-5.5.27/my-huge.cnf
/usr/share/doc/mysql-server-5.5.27/my-large.cnf
/usr/share/doc/mysql-server-5.5.27/my-medium.cnf
/usr/share/doc/mysql-server-5.5.27/my-small.cnf
/usr/share/doc/mysql-server-5.5.27/my-innodb-heavy-4g.cnf
/usr/share/doc/kpathsea-2007/kpathsea_defaults/texmf-kpathsea-defaults.cnf
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf
/usr/share/mysql/my-innodb-heavy-4g.cnf
/etc/pki/tls/openssl.cnf

Once found, copy one out
# CP/USR/SHARE/MYSQL/MY-HUGE.CNF/ETC/MY.CNF
Open MY.CNF Modify Encoding
# VI/ETC/MY.CNF

MySQL 5.5 or less:
Add under [mysqld]
Default-character-set=utf8
Add under [client]
Default-character-set=utf8

MySQL 5.5 instead:
[Client]
Character-set-server = UTF8
[Mysqld]
Character-set-server = UTF8
}

To modify the character set when logging in:
Mysql-uroot-p ' abc123,. '--default-character-set=latin1

Check:
Sed-n ' 18,19p '/etc/my.cnf
[Client]
#default-character-set=utf8
#password = Your_password

Why does the server need to be restarted to take effect:
Because Linux wants to load the configuration into memory.

Specify the character set to build the library:
Create database names Defalut CHARACTER SET UTF8 COLLATE utf8_general_ci;
Specify a character set to build a table:
CREATE TABLE ' Student ' (
' ID ' int (4) not NULL auto_increment,
' Name ' char (a) is not NULL,
PRIMARY KEY (' id ')
) Engine=innodb auto_incerment=10 Dafault Charset=utf8

The character set must be consistent:
Table
Library
/etv/my.cnf
[Mysqld] Service side
[Client] Clients
Linux
/etc/sysconfig/i18n
Development Program

How to change the production MySQL database library table character set
For an existing database, you cannot modify the character set by "Alter Adtabase character set*" or "ALTER TABLE TableName character set*"
Neither command can update the character set of the existing record, only the newly created table or record
There are already recorded character set adjustments, you must first export the data, after modifying the character set after re-import to complete the modification.


1. Export Table structure
Mysqldump-uroot-p--default-character-set=latinl-d Dbname>alltable.sql
--DEFAULT-CHARACTER-SET=GBK indicates that the character set is connected to the-D guide table structure
2. Edit Alltable.sql to change latin1 to GBK

3, ensure that the database is no longer updated, export so the data
Mysqldump-uroot-p--quick--no-create-info--extend-insert--default-character-set=latin1 dbname >alldata.sql
Parameter description:
--quick: Used to dump large tables, forcing mysqldump to retrieve data from the server one row at a time instead of retrieving all rows, and outputting the front cache into memory
--no-create-info: CREATE TABLE statement is not created--extend-insert: Use a multiline insert syntax that includes several values lists, so that the file is smaller, the IO is small, and the data is imported very quickly. --default-character-set=latin1: #按照原有的字符集导出数据 so that all Chinese in the exported file is visible and will not be stored as garbled.
4, open Alldata.sql set names Latin1 modified to set names GBK;
5. Build the Library
Create DATABASE Daname default CharSet GBK;
6, create the table, execute Allable.sql
Mysql-uroot-p Dbaneme <alltable.sql
7. Import data:
Mysql-uroot-p Dbaneme <alldata.sql

Attention:
When selecting a target character set, it is best to be aware that it is greater than or equal to the source character set (the font size is larger), otherwise the unsupported data may be lost.


Summarize:
1, export table structure and build the table statement, SED batch change
2, export so the data
3. Modify MySQL server and client code
4, delete the original library table and data
5. Import new database and build table statement
6. Import all MySQL data
Inser into Course (CNO,CNAME,TNO) VALUES (1, computer, 1);

MySQL Character set