1.å建ç¨æ·å¹¶ææ
ä¸è¬æ°å»ºæ°æ®åºé½éè¦æ°å¢ä¸ä¸ªç¨æ·ï¼ç¨äºç¨åºè¿æ¥ï¼è¿ç±»ç¨æ·åªéè¦insertãupdateãdeleteãselectæéã
æ°å¢ä¸ä¸ªç¨æ·ï¼å¹¶ææå¦ä¸ï¼
(1)grant select,delete,update,insert on *.* to admin@'192.168.0.%' identified by '123456';
flush privileges;
(2)æ°å»ºç¨æ·åææ
create user 'test'@'localhost' identified by 'test';
GRANT ALL ON db1.* TO 'test'@'localhost';
2.å建æ°æ®åºå¹¶æå®å符é
create database test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
3.æ¾ç¤ºæ°æ®åº
show databases;
show databases like '%test%';
æ¾ç¤ºå½åæ°æ®åº
select database();
4.使ç¨æ°æ®åº
use test;
æ¾ç¤ºå½åç¨æ·
select user();
5.å é¤æ°æ®åº
drop database test;
6.å é¤ç¨æ·(ç³»ç»å¤ä½è´¦å·)
(1)drop user "user"@主æºå(主æºå大åå ä¸æ)
(2)delete from mysql.user where user = 'root' and host = 'test';
flush privileges;
7.æ¥çæ°æ®åºçæ¬
select version();
8.æ¥çå½åæ¶é´
select now();
9.æ¥çç¨æ·å表
select user,host from mysql.user;
10.æ¥çç¨æ·æé
show grants for [email protected];
11.ææ主æºè¿ç¨è¿æ¥æ°æ®åº
12.ä¼ä¸ç¯å¢ææ
(1)对äºwebè¿æ¥ç¨æ·ææå°½ééç¨æå°åååï¼å¾å¤å¼æºè½¯ä»¶ è¿éè¦create,dropçæ¯è¾å±é©çæé
(2)çææ°æ®åºè¡¨åï¼è¦æ¶åcreate/dropææ
revoke insert on *.* FROM 'test'@'localhost';
ç产ç¯å¢é对主åº(åä¸ºä¸»è¯»ä¸ºè¾ )ç¨æ·çææï¼
åé¢å说
13.建表
create table student(
id int(4) not null,
name char(20) not null,
age tinyint(2) not null default '0',
dept varchar(16) default null
)ENGINE=InnoDB DEFAULT CHARSET=uft8;
14.å¢å 表å段
alter table test add a varchar(20);
15.æ´æ¹è¡¨æ
rename table test to test1;
16mysqlæå ¥ä¸æä¸ä¹±ç ç5ç§æ¹æ³
(1)ç»å½mysqlï¼å å"set names latin1"ï¼ç¶ååæ§è¡è¯å¥æè¯å¥æ件
(2)å¨sqlæ件ä¸æå®set names latin1ï¼ç¶åç»å½mysqlï¼éè¿å¦ä¸å½ä»¤æ§è¡
mysql>source test.sql
(3)å¨sqlæ件ä¸æå®set names latin1;ç¶åéè¿mysqlå½ä»¤å¯¼å ¥æ°æ®
mysql -uroot -poldboy123 oldboy
mysql -uroot -poldboy123 oldboy -e "set names latin1;select * from oldboy.test";
(4)éè¿æå®mysqlå½ä»¤çå符éåæ°å®ç°--default-character-set=latin1
cat test.sql
INSERT INTO student VALUES (9,'å¼ ä¸');
mysql -uroot -poldboy123 --default-character-set=latin1 oldboy
(5)å¨é ç½®æ件é设置客æ·ç«¯åæå¡ç«¯ç¸å ³åæ°
æ´æ¹my.cnf客æ·ç«¯æ¨¡åçåæ°ï¼å¯ä»¥å®ç°set names latin1çææï¼å¹¶ä¸æ°¸ä¹ çæ
[client]
default-character-set=latin1
æ示:æ ééå¯æå¡ï¼éåºéæ°ç»å½å°±çæãç¸å½äºset names latin1;
æ´æ¹my.cnfåæ°
[mysqld]
default-character-set=latin1éå5.1å以ä¸çæ¬
character-set-server=latin1éå5.5
ä¸ä¹±ç çææ³ï¼linuxï¼å®¢æ·ç«¯ï¼æå¡ç«¯ï¼åºï¼è¡¨ï¼ç¨åºä¿è¯å符éç»ä¸
ä¸ä¹±ç çææ³ï¼å»ºè®®ä¸è±æç¯å¢éæ©utf8
建åºï¼create database oldboy DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci
建表ï¼ä¸è¬æ åµä¸ï¼å»ºåºæ¯æå®å符éï¼è¡¨ä¸ç¨ç®¡
create table student
(id int(4) not null auto_increment,
name char(20) not null,
primary key(id )
)ENGINE= InnoDB AUTO_INCREMENT=10 DEFAULT CHARATERSET=UTF8;
æ¥çæ ¡å¯¹è§åshow character set;
æ¥çç¶æ
show variables;
show global status;
set global key_buffer_size=
show variables liek 'key_buffer%'
ä½ä¸ºä½ä¸æ»ç»
mysqldump
mysqlbinlog
mysql
mysqladmin
客æ·ç«¯å符é
character_set_client
character_set_connection
character_set_results
set names就该æ´æ¹äºä¸é¢ä¸ä¸ªåéçå¼
ç¡®ä¿mysqlæ°æ®åºæå ¥æ°æ®ä¸ä¹±å解å³æ¹æ¡
ç»ä¸mysqlæ°æ®åºå®¢æ·åæå¡ç«¯å符é
1.é常ï¼mysqlæ°æ®åºçä¸é¢å 个å符é(客æ·ç«¯åæå¡ç«¯)ç»ä¸æä¸ä¸ªå符éï¼æè½ç¡®ä¿æå ¥çä¸ææ°æ®å¯ä»¥æ£ç¡®è¾åºãå³mysql>show variables like 'character_set%' ç»æä¸çå符é设置尽éç»ä¸ãå½ç¶linuxç³»ç»çå符éä¹è¦å°½å¯è½åæ°æ®åºå符éç»ä¸ã
2ãshow variables like 'character_set%';å½ä»¤ç»æè¾åºå¦ä¸
variable_name |value
1character_set_client |latin1 #客æ·ç«¯å符éï¼set names latin1;
2character_set_connection |latin1 #è¿æ¥å符éï¼set names latin1;
3character_set_database |latin1 #æ°æ®åºå符éï¼é ç½®æ件å¶å®æ建åºå»ºè¡¨å¶å®ã
4character_set_results |latin1 #è¿åç»æå符éï¼set names latin1;
5character_set_server |latin1 #æå¡å¨å符éï¼é ç½®æ件å¶å®æ建åºå»ºè¡¨å¶å®ã
å ¶ä¸ï¼1ã2ã4 3个åæ°é»è®¤æ åµéç¨linuxç³»ç»å符é设置ï¼äººå·¥ç»å½æ°æ®åºæ§è¡âset names latin1â以åmysqlå¶å®å符éç»å½æä½ï¼é½æ¯æ¹åäºmysql客æ·ç«¯çclientãconnectionãresults3个åæ°çå符é为latin1ï¼ä»è解å³äºæå ¥ä¸æä¹±ç çé®é¢ï¼è¿ä¸ªæä½ä¹å¯ä»¥éè¿æ´æ¹my.cnfå®ç°mysql客æ·ç«¯çå符éï¼é ç½®æ¹æ³å¦ä¸ã
[client]
default-character-set=latin1
æ示ï¼æ ééå¯æå¡ï¼éåºéæ°ç»å½å°±çæãæ¤åæ°ç¸å½äºï¼ç»å½åæ§è¡set names latin1;
[mysqld]
character-set=latin1
æ示ï¼éè¦éå¯æå¡
æ´æ¹çåæ°æ¯character_set_databaseåcharacter_set_server
说æï¼
default-character-set=gbk <=éå5.1å以åççæ¬
character-set-server=gbk <=éå5.5
ç»è®ºï¼
ä¸ä¹±ç çææ³ï¼å»ºè®®ä¸è±æç¯å¢éæ©utf8
1ãlinuxç³»ç»
#cat /etc/sysconfig/i18n
æ示ï¼linux客æ·ç«¯ä¹è¦æ´æ¹å符串ï¼ä¾å¦ï¼SecureCRT->
2ãmysql客æ·ç«¯
临æ¶ï¼set names utf8;
æ°¸ä¹ ï¼æ´æ¹my.cnf客æ·ç«¯æ¨¡åçåæ°ï¼å¯ä»¥å®ç°set names utf8çææï¼å¹¶ä¸æ°¸ä¹ çæã
[client]
default-character-set=utf8
æ示ï¼æ ééå¯æå¡ï¼éåºéæ°ç»å½å°±çæï¼ç¸å½äºset names utf8;
3.mysqlæ°æ®åºçæå¡ç«¯
æ´æ¹my.cnf
[mysqld]
default-character-set=utf8 éå5.1å以åççæ¬
character-set-server=utf8 éå5.5
4ãmysqlæ°æ®åºä¸å»ºåºå»ºè¡¨
建åºï¼create database oldboy DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci
建表ï¼ä¸è¬æ åµä¸ï¼å»ºåºæ¯æå®å符éï¼è¡¨ä¸ç¨ç®¡
create table student
(id int(4) not null auto_increment,
name char(20) not null,
primary key(id )
)ENGINE= InnoDB AUTO_INCREMENT=10 DEFAULT CHARATERSET=UTF8;
æ´æ¹ç产线ä¸Mysqlæ°æ®å符éçæ¹æ¡
æ´æ¹æ°æ®åº/表çå符é
æ°æ®å符éä¿®æ¹æ¥éª¤
对äºå·²æçæ°æ®åºæ³ä¿®æ¹å符éä¸è½ç´æ¥éè¿"alter database character set *"æâalter tablename character set *"è¿ä¸¤ä¸ªå½ä»¤é½æ²¡åæ´æ°å·²æè®°å½çå符éï¼èåªæ¯å¯¹æ°å建ç表æè è®°å½çæã
å·²ç»æè®°å½çå符çè°æ´ï¼å¿ é¡»å å°æ°æ®å¯¼åºï¼è®²è¿ä¿®æ¹å符éåéæ°å¯¼å ¥åæå¯å®æã
1.导åºè¡¨ç»æ
msyqldump -uroot -p --default-character-set=latin1 -d dbname>alltable.sql
--default-character-set=gbk表示以GBKå符éè¿è¡è¿æ¥ -d åªå¯¼è¡¨ç»æ
2.ç¼è¾alltable.sqlå°latin1æ¹æGBK
3.ç¡®ä¿æ°æ®åºä¸åæ´æ°,ï¼å¯¼åºæææ°æ®
mysqldump -uroot -p --quick --no-create-info --extended-insert --default-charater-set=latin1 dbname>alldata.sql
åæ°è¯´æï¼
--quick:ç¨äºè½¬å¨å¤§ç表ï¼å¼ºå¶mysqldumpä»æå¡å¨ä¸æ¬¡ä¸è¡çæ£ç´¢æ°æ®èä¸æ¯æ£ç´¢ææè¡ï¼å¹¶è¾åºåCACHEå°å åä¸
--no-create-infoï¼ä¸å建CREATE TABLEè¯å¥
--extended-insertï¼ä½¿ç¨å æ¬å 个VALUESå表çå¤è¡INSERTè¯æ³ï¼è¿æ ·æ件æ´å°ï¼IOä¹å°ï¼å¯¼å ¥æ°æ®æ¶ä¼é常快
--default-charater-set=latin1 #æç §åæå符é导åºæ°æ®ï¼è¿æ ·å¯¼åºçæ件ä¸ï¼ææä¸æé½æ¯å¯è§çï¼ä¸ä¼ä¿åæä¹±ç
4.æå¼alldata.sqlå°set names latin1ä¿®æ¹æ set names GBK;(æè ä¿®æ¹ç³»ç»çæå¡ç«¯å客æ·ç«¯)
5.建åº
create database dbname default charset gbk;
6.å建表ï¼æ§è¡alltable.sql
mysql -uroot -p dbname
7.å¯¼å ¥æ°æ®
mysql -uroot -p dbname
æ»ç»ï¼Latin1->utf8
1ã建åºå建表çè¯å¥å¯¼åºï¼sedæ¹éä¿®æ¹ä¸ºutf8
2ã导åºæææ°æ®
3ãä¿®æ¹mysqlæå¡ç«¯å客æ·ç«¯ç¼ç 为utf8
4ãå é¤åæçåºè¡¨åæ°æ®
5ãå¯¼å ¥å»ºåºå建表çè¯å¥
6ãå¯¼å ¥mysqlçæææ°æ®