Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "mysql database application management + garbled + character set example analysis", the content is easy to understand, well-organized, hope to help you solve your doubts, the following let the editor lead you to study and learn "mysql database application management + garbled + character set example analysis" this article.

Insert

Test table mysql > show create table test\ G

Create table test (

Id int (4) not null AUTO_INCREMENT

Name char (20) not null

Primary key (id)

);

Mysql > insert into test (id,name) value (1pm Hequan')

Mysql > select * from test

Mysql > insert into test (name) value ('hequan'); / / ID is self-increasing and name can be inserted.

Mysql > insert into test value (3) (4) (4); / / No columns, but insert them in order

Mysqldump-uroot-p123456-B oldboy > / tmp/oldboy_bak.sql / / backup database backup check

Grep-E-v "# |\ / | ^ $|--" / tmp/oldboy_bak.sql

Select from where

Mysql > select id,name from test where name='hequan' and/or id=4

Mysql > select id,name from test limit 0Pol 2; / / start with line 0 and look up line 2

Mysql > select id,name from test where id > 2 and id select id,name from test order by id asc/desc

Multi-table query

Mysql > select student.Sno,student.Sname,course.Cname,SC.Grade from student,course,SC where student.Sno=SC.Sno and course.Cno=SC.Cno order by Sno

Mysql > explain select * from test where name='hequan'\ Grabble / execute process to determine whether the index is running or not

Possible_keys: NULL

Rows: 2

Mysql > create index index_name on test (name)

Possible_keys: index_name

Rows: 1

Update

Mysql > update test set name='xx' where id=4

Mysql-uroot-p123456 oldboy

< /tmp/oldboy_bak.sql //恢复数据，增量恢复。 增量恢复 #log-bin=mysql-bin 打开 /application/mysql/data/mysql-bin-hequan.000001 mysqlbinlog mysql-bin-hequan.000001 mysqladmin -uroot -p123456 flush-log 切割日志 mysql-bin-hequan.000002 mysqlbinlog -d oldboy mysql-bin-hequan.000001 >

Bin.sql

Delete the wrong statement

Mysql-uroot-p123456 oldboy delete from test where id=3; > truncate table test; / / clear the table

Change the fields of a table

Mysql > alter table test add sex char (4) after name; / / add sex / / first after name

Mysql > rename table test to test1

Mysql > alter table test1 rename to test

Mysql > drop table test

Garbled code

Unified character set / etc/my.cnf changes 3 restart services

Set names utf8

Cat / etc/sysconfig/i18n / / system environment

LANG= "zh_CN.UTF-8"

Vim / etc/my.cnf / / Server side and client side

[client]

Default-character-set=utf8

[mysqld]

Character-set-server=utf8 / / 5.5

Default-character-set=utf8 / / 5.1

[mysql]

Default-character-set=utf8

Show variables like 'character_set%'

The SecureCRT client will be changed to utf8

Database character set GBK UTF-8 latin1

Mysql > show character set

+-- +

| | Charset | Description | Default collation | Maxlen | |

+-- +

| | utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

| | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |

| | latin1 | cp1252 West European | latin1_swedish_ci | 1 |

| | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |

Mysql > show variables like 'character_set%'

+-+

| | Variable_name | Value |

+-+

| | character_set_client | utf8 | client |

| | character_set_connection | utf8 | Link |

| | character_set_database | latin1 | Database character set |

| | character_set_filesystem | binary |

| | character_set_results | utf8 | returned result |

| | character_set_server | latin1 | Server character set |

| | character_set_system | utf8 |

| | character_sets_dir | / application/mysql/share/charsets/ |

+-+

Mysql-uroot-p123456-- default-character-set=latin1

Set names latin1

Take effect temporarily

Mysql changes the character set of the existing data table and retains the original data content

Environment: the character set is not set correctly at the beginning of the application. After running for a period of time, it is found that the character set can not be adjusted to meet the needs, and you do not want to discard the data during this period of time, so you need to modify the character set. Character set changes cannot be made directly through the "alter database character set * *" or "alter table tablename character set * *" commands, neither of which updates the character set of existing records, but only takes effect on newly created tables or records.

So what do you need to do with the character set adjustment that has been recorded?

The following simulates the process of modifying a database of the latin1 character set to a database of the GBK character set:

(1) Export table structure

Mysqldump-uroot-p-- default-character-set=gbk-d name > createdb.sql

Where-default-character-set=gbk indicates what character set to connect with, and-d indicates that only the table structure is exported and no data is exported.

(2) manually modify the character set in the table structure definition in createdb.sql to the new character set.

Sed-I's slave latin1 'gbk' g 'createdb.sql

(3) ensure that the records are no longer updated and export all records

Mysqldump-uroot-p-quick-no-create-info-extended-insert-default-character-set=latin1 name > data.sql

-- quick: this option is used to dump large tables. It forces Mysqldump to retrieve rows in the table one row at a time from the server, instead of retrieving all rows, caching it in memory before output

-- extended-insert: use a multiline Insert syntax that includes several values lists to make the dump file smaller and reload the file faster

-- no-create-info: disdain to recreate the create table statement of each dump table

-- default-character-set=latin1: export all data according to the original character set, so that all Chinese characters in the exported file are visible and will not be saved as garbled.

(4) Open data.sql and change SET NAMES latin1 to SET NAMES gbk

(5) create a new database with a new character set

Create database newdatabasename default charset gbk

(6) create a table and execute createdb.sql

Mysql-uroot-p newdatabasename < createdb.sql

(7) Import data and execute data.sql

Mysql-uroot-p newdatabasename < data.sql

(8) before checking, make sure that the local environment is changed to gbk mode. You can use temporary changes for testing

Set names gbk

The above is all the contents of the article "mysql Database Application Management + garbled + sample Analysis of character set". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.