以前主要使用oracle做数据库，现在换成mysql了，发现不一样的地方还是挺多的，记录一下：
一、centos上的yum install方式安装
完全卸载(可选，如果之前安装了旧版本)
a) rpm -qa|grep mysql
先查看是否已经安装了mysql
b) yum remove mysql*
执行完成后，再执行下a)中的命令确认下
c)
rm -f /etc/my.cnf
rm -f /etc/my.cnf.rmp*
rm -rf /var/lib/mysql
安装最新版mysql
a) 找到最新的版本
b) 把这个文件上传到服务器，比如：/data/download 下 找到rpm的下载（目前最新是5.7版，8.6k）
c) 导入yum库
yum localinstall /data/download/mysql57-community-release-el6-7.noarch.rpm
d) 安装
yum install mysql-community-server
以下是安装后的几个关键目录默认位置：
数据库目录
/var/lib/mysql/
配置文件
/usr/share/mysql（mysql.server命令及配置文件）
相关命令
/usr/bin（mysqladmin mysqldump等命令）
启动脚本
/etc/rc.d/init.d/（启动脚本文件mysql的目录）
查看配置文件位置
mysqld --verbose --help |grep -A 1 'Default options’
会输出类似下面的信息：
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
这表示mysql启动时会查找/etc/my.cnf，如果找不到，则到路径/etc/mysql/my.cnf，依此类推...
修改my.cnf
参考配置：
1 [client] 2 3 default-character-set=utf8 4 5 [mysqld] 6 7 default-character-set=utf8 8 9 character_set_server=utf8 10 11 # innodb_buffer_pool_size = 128M 12 13 # log_bin 14 15 # join_buffer_size = 128M 16 17 # sort_buffer_size = 2M 18 19 # read_rnd_buffer_size = 2M 20 21 datadir=/data/mysql/db 22 23 socket=/var/lib/mysql/mysql.sock 24 25 symbolic-links=0 26 27 log-error=/data/mysql/log/mysqld.log 28 29 pid-file=/data/mysql/mysqld.pid
这里有几个关键参数，需要修改
default-character-set
character_set_server
这个是设置utf8编码，可以解决大多数中文乱码问题
datadir
log-error
pid-file
分别对应数据库文件目录位置，日志文件位置，pid文件位置，建议调整到剩余空间较大的分区
innodb_buffer_pool_size 这个是会影响mysql的性能，后面还会讲到
启动mysqld
a) 调整目录权限
chown -R mysql:mysql /data/mysql
启动前，建议先确认下相关目录，mysql有没有读写权限，否则启动会失败
service mysqld start
修改root密码：
mysql 5.7对用户安全性做了加强，默认root账号是无法登录的，修改方法如下：
service mysqld stop //先停止
mysqld_safe --skip-grant-tables //以安全模式启动
另开一个ssh终端窗口
mysql //进入mysql控制台
update user set authentication_string=password(‘新密码') where user='root’;
注：新密码必须复杂安全性要求，建议弄成A1b2c3@def.com这种复杂的
重启mysql
service mysqld stop
service mysqld start
现在mysql -uroot -p 应该能登录进去了
如果进去执行其它操作时，比如创建数据库时，提供要重设密码之类的，mysql命令行模式下再执行一遍下面的操作
set password=password(‘新密码’);
二、创建数据库及用户授权
特别要注意编码
create database xxx default character set utf8;
(注：从5.0.2开始，创建数据库也可以用create schema命令，这二者在mysql中等效的，这跟其它主流关系型数据库，比如：oracle,ms sql中的schema概念完全不同)
如果建错了，想删除数据库
drop database xxx;
切换数据库
use xxx
查看所有数据库
show database;
用户授权：
GRANT ALL PRIVILEGES ON db1.* TO ‘user1’@‘localhost’ IDENTIFIED BY ‘pwd1’;
上面的语句将db1的所有权限授权给用户user1，如果只想授权部分权限，参考下面的示例：
grant select on table2 to 'user1'@'localhost';
三、一些常用的sql操作
a) 执行外部sql脚本文件
mysql命令行下
source /opt/app/sql/xxx.sql (注：xxx.sql必须存在，且mysql账号必须有权限读取)
b）查看当前正在运行的sql
show processlist
通常mysql运行很卡时，用这个命令查找当前正在跑的sql，然后找到其id,方便将其kill掉
c) kill掉指定id的sql操作
mysqladmin -h 服务器 -u用户名 -p kill id号
d) 查看当前数据库引擎状态
show engine innodb status\G;
e) 查看几个关键参数：
show global status like 'innodb_buffer_pool_pages_data';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 4053 |
+-------------------------------+-------+
1 row in set (0.01 sec)
show global status like 'innodb_buffer_pool_pages_total’;
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_total | 4095 |
+--------------------------------+-------+
1 row in set (0.01 sec)
这二个的比值，即innodb_buffer_pool_pages_data/innodb_buffer_pool_pages_total ，按网上的经验之谈，如果>95%，说明mysql内存快满负载了，建议大innodb_buffer_pool_size的值 ，建议设置成系统内存的75%
注：select @@innodb_buffer_pool_size 显示出来的值
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 8388608 |
+---------------------------+
1 row in set (0.00 sec)
是以字节为单位的，要转换成M，需要除1024*1024，上面的值8388608，即相当于8388608/(1024*1024)=8M
f) 数据导出
导出整个数据库(包括数据)
mysqldump -h服务器ip -u 用户名 -p 数据库名 > 导出的文件名
导出单个表(包括数据)
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
仅导出表结构
./mysqldump -u用户名 -p -d --add-drop-table 数据库名 > 导出的文件名
仅导出数据
./mysqldump -u用户名 -p -t 数据库名 > 导出的文件名
导出后的sql脚本，可以在目标数据库上，通过前面提到的source命令导入
g) 查看所有表/视图/存储过程
show tables;
SELECT * from information_schema.VIEWS\G;
show procedure status\G;
h)查看表结构、视图结构、存储过程sql
show create table 表名\G;
show create view 视图名\G;
show create function 函数名\G;
show create procedure 存储过程名\G;
show create database 数据库名\G;
i) update ...join... on 操作
1 UPDATE table12 INNER JOIN table2 ON (3 table1.id = table2.id4 )5 SET table1.x = table2.y;
j) 快速复制一张表
create table table1_bak select * from table1;
k) 将一张表的某些记录快速插入相同结构的备份表中
insert into table1_bak select * from table1 limit 0,5;
l) 跨库查询1 SELECT
2 count(*) 3 FROM 4 db1.table1 t1 5 INNER JOIN db2.table2 t2 ON t1.id = t2.id 6 WHERE 7 t1.id > 0
前提：当前用户有db2.table2的select权限，如果没权限，先按前面的用户授权方法给相对的表授权。
四、自定义函数，游标，存储过程
a) 先解决命令行模式下；号的问题
因为；是默认的命令结束符号，写自定义函数或存储过程的时候，本身就会包含;符号，导致命令行下，mysql误认为存储过程代码结果，解决办法
delimiter //
上面的命令告诉mysql，命令结束符号为//，而不是默认的;
存储过程写完了以后，再执行
delimiter ;
还原回来
b) 自定义函数示例
1 DELIMITER // 2 DROP FUNCTION 3 IF EXISTS `ifempty`// 4 5 CREATE FUNCTION `ifempty`( 6 s1 VARCHAR(4096), 7 s2 VARCHAR(4096) 8 ) 9 RETURNS VARCHAR(4096) 10 CHARSET utf8 NO SQL DETERMINISTIC SQL SECURITY INVOKER 11 BEGIN 12 13 IF (ISNULL(s1)) 14 THEN 15 RETURN s2; 16 ELSEIF CHAR_LENGTH(s1) = 0 17 THEN 18 RETURN s2; 19 ELSE 20 RETURN s1; 21 END 22 IF; 23 END//
c) 存储过程示例
1 DELIMITER // 2 3 CREATE PROCEDURE test(IN b INT) 4 5 BEGIN 6 7 DECLARE a INT; 8 9 SET a = b + 1; 10 11 SELECT a; 12 13 END 14 15 //
d）三种常用的循环写法
while..do 写法
1 DELIMITER // 2 3 CREATE PROCEDURE test(IN b INT) 4 5 BEGIN 6 7 DECLARE i INT; 8 9 SET i = 0; 10 11 WHILE i < b DO 12 13 SELECT i; 14 15 SET i = i + 1; 16 17 END WHILE; 18 19 END 20 21 //
repeat 写法
1 DELIMITER // 2 3 CREATE PROCEDURE test(IN b INT) 4 5 BEGIN 6 7 DECLARE i INT DEFAULT 0; 8 9 REPEAT 10 11 SELECT i; 12 13 SET i = i + 1; 14 15 UNTIL i >= b 16 17 END REPEAT; 18 19 END 20 21 //
loop .. end loop写法
1 DELIMITER // 2 3 CREATE PROCEDURE test(IN b INT) 4 5 BEGIN 6 7 DECLARE i INT DEFAULT 0; 8 9 mylabel: LOOP 10 11 SELECT i; 12 13 SET i = i + 1; 14 15 IF i >= b 16 THEN 17 18 LEAVE mylabel; 19 20 END IF; 21 22 END LOOP; 23 24 END 25 26 //
e) 游标示例
1 DELIMITER // 2 3 CREATE PROCEDURE test(IN min_id INT) 4 5 BEGIN 6 7 DECLARE _done INT DEFAULT 0; 8 -- 判断游标是否结束的标志 9 10 DECLARE p_id INT DEFAULT 0; 11 12 DECLARE p_name VARCHAR(100) DEFAULT ''; 13 14 DECLARE _cur CURSOR FOR 15 16 SELECT 17 t.`d_id`, 18 t.`d_name` 19 FROM t_test AS t 20 WHERE t.`d_id` >= min_id; 21 22 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1; 23 -- 标记循环结束 24 25 OPEN _cur; 26 27 REPEAT 28 29 FETCH _cur 30 INTO p_id, p_name; 31 32 IF NOT _done 33 THEN 34 35 SELECT 36 p_id, 37 p_name; 38 39 END IF; 40 41 UNTIL _done 42 43 END REPEAT; 44 45 CLOSE _cur; 46 47 END 48 49 //
当然，也可以将游标的遍历换成while do ...end while的写法
1 DELIMITER // 2 3 DROP PROCEDURE IF EXISTS p_test_cursor // 4 5 CREATE PROCEDURE p_test_cursor() 6 7 BEGIN 8 DECLARE _done INT DEFAULT 0; -- 判断游标是否结束的标志 9 10 DECLARE p_activity_id INT DEFAULT 0; 11 DECLARE p_community_id INT DEFAULT 0; 12 13 DECLARE _cur CURSOR FOR 14 SELECT 15 t.activity_id, 16 t.community_id 17 FROM h_activity_community AS t; 18 19 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1; -- 标记循环结束 20 21 OPEN _cur; 22 23 WHILE _done != 1 24 DO 25 FETCH _cur 26 INTO p_activity_id, p_community_id; 27 28 IF (_done != 1) -- 如果游标没结束,就打印出这些变量值 29 THEN 30 SELECT 31 p_activity_id, 32 p_community_id, 33 _done; 34 END IF; 35 36 END WHILE; 37 38 CLOSE _cur; 39 COMMIT; 40 END 41 //
注：mysql的游标是以临时表实现的，性能不怎么样，如果游标中处理上十万条数据，就比较慢。
随时随地看视频
