5.mysql应用管理实践(2)

=============================
19.DML语句之insert往表中插入数据
（1）语法：
insert into <表名> [(<字段名1>[,...<字段名n>])]values(值1)[,(值n)]
（2）新建一个简单的测试表
create table test(
id int(4) NOT NULL AUTO_INCREMENT,
name char(20) NOT NULL,
PRIMARY KEY(id)
);
mysql> create table test(
-> id int(4) NOT NULL AUTO_INCREMENT,
-> name char(20) NOT NULL,
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.05 sec)

（3）插入数据
法一：按规定指定所有列名，并且每列都插入值。
mysql> insert into test(id,name) values(1,'oldboy');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;//查看表信息
+----+--------+
| id | name |
+----+--------+
| 1 | oldboy |
+----+--------+
1 row in set (0.00 sec)
mysql> insert into test(name) values('oldgirl');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
+----+---------+
2 rows in set (0.00 sec)

法二：若不指定列，必须按顺序为每列插入恰当的值。
mysql> insert into test values(3,'inca');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
+----+---------+
3 rows in set (0.00 sec)

法三：批量插入数据方法，提升效率。
mysql> insert into test values(4,'meng'),(5,'ming');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
+----+---------+
5 rows in set (0.00 sec)

mysql> delete from test;//删除表test
Query OK, 5 rows affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> insert into test values(1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'meng'),(5,'ming');//优化
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
+----+---------+
5 rows in set (0.00 sec)

（4）测试完成，退出数据库，备份上述数据留用。
mysql> use oldboy
Database changed
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| student |
| test |
+------------------+
2 rows in set (0.00 sec)
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -B oldboy >/opt/oldboy_bak.sql //备份数据库oldboy，逻辑备份
[root@oldboyedu-01 ~]# ll /opt/oldboy_bak.sql
-rw-r--r-- 1 root root 2826 Jan 13 14:03 /opt/oldboy_bak.sql
[root@oldboyedu-01 ~]# grep -E -v "#|\/|^$|--" /opt/oldboy_bak.sql //查看导出的数据库

（5）说明：平时登录网站发帖子发博文，实质是调用web网站的程序连接mysql数据库，通过上述的insert语句把帖子博文数据存入数据库的。

=============================
20.DQL语句之select查询数据
（1）语法：select <字段1，字段2，...> from <表名> where <表达式>
其中，select,from,where不能随便修改，是关键字，支持大小写。
mysql> use oldboy
Database changed
（2）查询表的所有数据行，即某个表信息
法一：一般查询所有信息。
mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
+----+---------+
5 rows in set (0.00 sec)
法二：优化:查询的列列出来，不用*。
mysql> select id,name from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
+----+---------+
5 rows in set (0.00 sec)

（3）指定条件查询
mysql> select * from test limit 2;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
+----+---------+
2 rows in set (0.00 sec)
mysql> select * from test limit 3;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
+----+---------+
3 rows in set (0.00 sec)

mysql> select * from test limit 3,5;
+----+------+
| id | name |
+----+------+
| 4 | meng |
| 5 | ming |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from test where id=3;
+----+------+
| id | name |
+----+------+
| 3 | inca |
+----+------+
1 row in set (0.00 sec)

mysql> select * from test where id>3;
+----+------+
| id | name |
+----+------+
| 4 | meng |
| 5 | ming |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from test where name='oldgirl';//注意带引号！
+----+---------+
| id | name |
+----+---------+
| 2 | oldgirl |
+----+---------+
1 row in set (0.00 sec)

mysql> select * from test where name='oldgirl' and id=3;//多项查询
Empty set (0.00 sec)
mysql> select * from test where name='oldgirl' or id=3;
+----+---------+
| id | name |
+----+---------+
| 2 | oldgirl |
| 3 | inca |
+----+---------+
2 rows in set (0.00 sec)

（4）其他查询排序
mysql> select * from test order by id;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
+----+---------+
5 rows in set (0.00 sec)

mysql> select * from test order by id asc;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
+----+---------+
5 rows in set (0.00 sec)

mysql> select * from test order by id desc;
+----+---------+
| id | name |
+----+---------+
| 5 | ming |
| 4 | meng |
| 3 | inca |
| 2 | oldgirl |
| 1 | oldboy |
+----+---------+
5 rows in set (0.00 sec)

=============================
21.DQL语句之select多表查询数据
（1）创建关联表语句
学生表：Student(Sno,Sname,Ssex,Sage,Sdept)
(学号-主键，姓名，性别，年龄，所在系)
create table student(
Sno int(10) NOT NULL COMMIT '学号',
Sname varchar(16) NOT NULL COMMIT '姓名',
Ssex char(2) NOT NULL COMMIT '性别',
Sage tinyint(2) NOT NULL default '0' COMMIT '学生年龄',
Sdept varchar(16) default NULL COMMIT '学生所在系列',
PRIMARY KEY (Sno),
key index_Sname(Sname)
);
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

课程表：Course(Cno,Cname,Ccredit)
(课程号-主键，课程名，学分)
create table course(
Cno int(10) NOT NULL COMMIT '课程号',
Cname varchar(64) NOT NULL COMMIT '课程名',
Ccredit tinyint(2) NOT NULL COMMIT '学分',
PRIMARY KEY (Cno)
)ENGINE=InnoDB AUTO_INREMENT=1,DEFAULT CHARSET=latin1;

选课表：SC(Sno,Cno,Grade)
(学号-主键，课程号-主键，成绩)-其中SC表主键参照Student,Course表。
create table `SC`(
SCid int(12) NOT NULL auto_increment COMMIT '主键'，
`Cno` int(10) NOT NULL COMMIT '课程号',
`Sno` int(10) NOT NULL COMMIT '学号',
`Grade` tinyint(2) NOT NULL COMMIT '学生成绩',
PRIMARY KEY(`SCid`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

待定。。。

mysql> select * from test where name='oldboy';
+----+--------+
| id | name |
+----+--------+
| 1 | oldboy |
+----+--------+
1 row in set (0.01 sec)

mysql> explain select * from test where name='oldboy'\G //判断是否走索引
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5 //扫描行的数目
Extra: Using where
1 row in set (0.00 sec)

mysql> create index index_name on test(name);//创建name索引
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select * from test where name='oldboy'\G //判断是否走索引
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ref
possible_keys: index_name
key: index_name
key_len: 60
ref: const
rows: 1 //扫描行的数目，快！
Extra: Using where; Using index
1 row in set (0.00 sec)
mysql> help explain;

=============================
23.DML之修改表中数据
（1）修改表中指定条件固定列的数据
语法：update 表名 set 字段=新值，...where 条件(一定要注意条件)

mysql> select * from test;//查看要修改的表
+----+---------+
| id | name |
+----+---------+
| 3 | inca |
| 4 | meng |
| 5 | ming |
| 1 | oldboy |
| 2 | oldgirl |
+----+---------+
5 rows in set (0.00 sec)
mysql> update test set name='gongli' where id=3;//修改指定的行字段的内容
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 3 | gongli |
| 4 | meng |
| 5 | ming |
| 1 | oldboy |
| 2 | oldgirl |
+----+---------+
5 rows in set (0.00 sec)

（2）修改表中所有行的数据

案例：625某电商网站数据库宕机故障解决实录（上）（下）
http://blog.51cto.com/oldboy/1431161

严重案例：不带条件更改所有表的记录，可用备份数据恢复！
mysql> update test set name='gongli';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 5 Changed: 4 Warnings: 0

mysql> select * from test;
+----+--------+
| id | name |
+----+--------+
| 1 | gongli |
| 2 | gongli |
| 3 | gongli |
| 4 | gongli |
| 5 | gongli |
+----+--------+
5 rows in set (0.00 sec)
解决：备份数据恢复，该方式缺点是有数据丢失！
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock </opt/oldboy_bak.sql
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock
......
mysql> use oldboy
Database changed
mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
+----+---------+
5 rows in set (0.00 sec)

=============================
24.初步增量恢复mysql数据库
[root@oldboyedu-01 ~]# vim /data/3306/my.cnf
#log-bin = /data/3306/mysql-bin
log-bin = /data/3306/mysqlbin_oldboy
[root@oldboyedu-01 ~]# /data/3306/mysql restart
Restarting MySQL...
Stoping MySQL...
Starting MySQL...
mysql> use oldboy
Database changed
mysql> update test set name='gongli';
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0

mysql> select * from test;
+----+--------+
| id | name |
+----+--------+
| 1 | gongli |
| 2 | gongli |
| 3 | gongli |
| 4 | gongli |
| 5 | gongli |
+----+--------+
5 rows in set (0.00 sec)

[root@oldboyedu-01 ~]# ll /data/3306/mysqlbin_oldboy.000001
-rw-rw---- 1 mysql mysql 298 Jan 13 16:42 /data/3306/mysqlbin_oldboy.000001
[root@oldboyedu-01 ~]# cat /data/3306/mysqlbin_oldboy.000001 ařZFstd!!!oldboyBEGINařZ^std!!!oldboyupdate test set name='gongli'ařZ[root@oldboyedu-01 ~]# Xshell
-bash: Xshell: command not found
[root@oldboyedu-01 ~]# mysqlbinlog /data/3306/mysqlbin_oldboy.000001
显示：update test set name='gongli'

mysql> insert into test values(6,'xiaozhang');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+-----------+
| id | name |
+----+-----------+
| 1 | gongli |
| 2 | gongli |
| 3 | gongli |
| 4 | gongli |
| 5 | gongli |
| 6 | xiaozhang |
+----+-----------+
6 rows in set (0.00 sec)
停库最好了，但没有。
[root@oldboyedu-01 ~]# cp /data/3306/mysqlbin_oldboy.000001 /opt/
[root@oldboyedu-01 ~]# mysqladmin -uroot -poldboy123 -S /data/3306/mysql.sock flush-log
[root@oldboyedu-01 ~]# ll /data/3306/mysqlbin_oldboy.00000
mysqlbin_oldboy.000001 //需要处理这个
mysqlbin_oldboy.000002 //以后往这里面写了

[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock </opt/oldboy_bak.sql //恢复
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "select * from oldboy.test;"
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
+----+---------+
[root@oldboyedu-01 ~]# mysqlbinlog -d oldboy /data/3306/mysqlbin_oldboy.000001 >mybin.sql
[root@oldboyedu-01 ~]# vim mybin.sql
干掉：或#update test set name='gongli'
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock oldboy <mybin.sql //恢复
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "select * from oldboy.test;"
+----+-----------+
| id | name |
+----+-----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
| 6 | xiaozhang |
+----+-----------+
好了。。。

=============================
案例：防止误操作mysql数据库
http://blog.51cto.com/oldboy/1321061
结论：
在mysql命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序拒绝执行。

=============================
27.删除表中数据
（1）delete删除表中的数据
语法：delete from 表名 where 表达式
mysql> select * from test;
+----+-----------+
| id | name |
+----+-----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
| 6 | xiaozhang |
+----+-----------+
6 rows in set (0.00 sec)

mysql> delete from test where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> delete from test where name='oldgirl';
Query OK, 1 row affected (0.00 sec)
mysql> delete from test where id>6;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+----+-----------+
| id | name |
+----+-----------+
| 3 | inca |
| 4 | meng |
| 5 | ming |
| 6 | xiaozhang |
+----+-----------+
4 rows in set (0.00 sec)
mysql> delete from test;//a.逻辑清除，按行删除
Query OK, 0 rows affected (0.00 sec)

（2）truncate清空表， //b.清空物理文件（物理删除）
mysql> truncate table test;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> alter table test add sex char(4);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table test add age int(4) after name;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table test add qq varchar(15) first;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| qq | varchar(15) | YES | | NULL | |
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| age | int(4) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
生产环境多个复杂添加修改多字段信息的案例

=============================
29.更改表名
语法：rename table 原表名 to 新表名
（1）更改表名test：
mysql> rename table test to testnew;
Query OK, 0 rows affected (0.06 sec)

（2）删除表testnew：
mysql> drop table testnew;
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| student |
+------------------+
1 row in set (0.00 sec)

=============================
30.mysql数据库插入中文数据乱码问题

mysql> use oldboy
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table test(
-> id int(4) NOT NULL AUTO_INCREMENT,
-> name char(20) NOT NULL,
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

判断是否乱码并解决：
mysql> insert into test values(6,'杰伦');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
| 6 | 杰伦 |
+----+---------+
6 rows in set (0.00 sec)
法一：
mysql> set names latin1;//设置字符集latin1
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(7,'杰伦');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
| 6 | ?? |
| 7 | 杰伦 |
+----+---------+
7 rows in set (0.00 sec)

mysql> set names utf8;//设置字符集utf8
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+--------------+
| id | name |
+----+--------------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
| 6 | 杰伦 |
| 7 | æ°ä¼¦ |
+----+--------------+
7 rows in set (0.00 sec)

法二：
mysql> system cat test.sql
set names latin1;
insert into test values(8,'过年');

mysql> quit
Bye
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock
......
mysql> use oldboy
Database changed
mysql> source /root/test.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
| 6 | ?? |
| 7 | 杰伦 |
| 8 | 过年 |
+----+---------+
8 rows in set (0.00 sec)

法三：通过mysql命令加字符集参数导入数据解决乱码，我这里默认貌似是utf8
[root@oldboyedu-01 ~]# cat test.sql
insert into test values(9,'张三');
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock --default-character-set=latin1 oldboy < test.sql
mysql> use oldboy
Database changed
mysql> select * from test;
+----+---------------+
| id | name |
+----+---------------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
| 6 | 杰伦 |
| 7 | æ°ä¼¦ |
| 8 | è¿‡å¹´ |
| 9 | å¼ ä¸‰ |
+----+---------------+
9 rows in set (0.00 sec)

=============================
31.mysql数据库插入中文数据乱码问题5中解决方法我这里默认貌似是utf8：
法一：登录mysql，先做set names utf8;然后执行更新语句或者执行语句文件。该方法临时有效！（退出重新登录执行则失效！）
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock
......
mysql> use oldboy;
Database changed
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| test |
+------------------+
1 row in set (0.00 sec)

mysql> show create table test;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
+----+---------+
5 rows in set (0.00 sec)
mysql> set names utf8; //
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(6,'老男孩'); //
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+-----------+
| id | name |
+----+-----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
| 6 | 老男孩 |
+----+-----------+
6 rows in set (0.00 sec)

法二：在sql文件中指定set names utf8;然后登录mysql，如下执行：
mysql> system cat /root/test.sql
set names utf8;
mysql> source /root/test.sql
Query OK, 0 rows affected (0.00 sec)

法三：在sql文件中指定set names utf8;然后通过mysql命令导入数据：
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock oldboy <test.sql
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock oldboy -e "set names utf8;insert into test values(7,'老男孩');select * from oldboy.test;"
+----+-----------+
| id | name |
+----+-----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
| 6 | 老男孩 |
| 7 | 老男孩 |
+----+-----------+

法四：通过指定mysql命令的字符集参数实现--default-character-set=utf8
[root@oldboyedu-01 ~]# cat test.sql
insert into test values(8,'老女孩');
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock --default-character-set=utf8 oldboy <test.sql
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock oldboy -e "select * from oldboy.test;"
+----+-----------+
| id | name |
+----+-----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
| 6 | 老男孩 |
| 7 | 老男孩 |
| 8 | 老女孩 |
+----+-----------+

法五：在配置文件里设置客户端及服务器相关参数
a.更改客户端模块的参数，可以实现set names utf8;的效果，并且永久生效。
配置文件/data/3306/my.cnf 中添加：default-character-set=utf8
提示：退出重新登录就生效。相当于set names utf8;

b.更改服务器端字符集办法：
配置文件/data/3306/my.cnf添加：
[mysqld]
character-set-server=utf8

小结：
不乱码思想：linux,客户端，服务端，库，表，程序！
[root@oldboyedu-01 ~]# cat /etc/sysconfig/i18n //linux
LANG="en_US.UTF-8"
SYSFONT="latarcyrheb-sun16"

a.更改客户端模块的参数，可以实现set names utf8;的效果，并且永久生效。
配置文件/data/3306/my.cnf 中添加：default-character-set=utf8
提示：退出重新登录就生效。相当于set names utf8;

b.更改服务器端字符集办法：
配置文件/data/3306/my.cnf添加：
[mysqld]
character-set-server=utf8

库、表：
create database oldboy_utf8 default character set utf8 collate utf8_general_ci;//c.建立一个名为oldboy_utf8的GBK字符集数据库。

程序:
选择合适的字符集版本程序！

===========================
mysql> show variables; //查看mysql的变量
mysql> show status; //查看mysql的状态
mysql> show global status;
[root@oldboyedu-01 ~]# grep key_buffer /data/3306/my.cnf //ISAM索引的缓冲
key_buffer_size = 16M

posted @ 2018-01-19 18:30 bkycrmn 阅读(139) 评论(0) 收藏举报

刷新页面返回顶部

5.mysql应用管理实践(2)

公告