mysql 学习笔记

以前主要使用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) http://dev.mysql.com/downloads/repo/ 找到最新的

Red Hat Enterprise Linux 6 / Oracle Linux 6 (Architecture Independent), RPM Package		8.6K	Download
(mysql57-community-release-el6-7.noarch.rpm)	MD5: 4c4d512821c9cdbb8987d1942db84d11

找到rpm的下载（目前最新是5.7版，8.6k） 

b) 把这个文件上传到服务器，比如：/data/download 下 

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 

参考配置：

[client]

default-character-set=utf8 

[mysqld]

default-character-set=utf8

character_set_server=utf8

# innodb_buffer_pool_size = 128M

# log_bin

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

datadir=/data/mysql/db

socket=/var/lib/mysql/mysql.sock 

symbolic-links=0 

log-error=/data/mysql/log/mysqld.log

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 statusG;

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.VIEWSG;

show procedure statusG;

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 操作

UPDATE table1
INNER JOIN table2 ON (
     table1.id = table2.id
)
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) 跨库查询

SELECT
    count(*)
FROM
    db1.table1 t1
INNER JOIN db2.table2 t2 ON t1.id = t2.id
WHERE
    t1.id > 0

前提：当前用户有db2.table2的select权限，如果没权限，先按前面的用户授权方法给相对的表授权。

 

四、自定义函数，游标，存储过程 

a) 先解决命令行模式下；号的问题

因为；是默认的命令结束符号，写自定义函数或存储过程的时候，本身就会包含;符号，导致命令行下，mysql误认为存储过程代码结果，解决办法

delimiter //

上面的命令告诉mysql，命令结束符号为//，而不是默认的; 

存储过程写完了以后，再执行

delimiter ;

还原回来

b) 自定义函数示例

DELIMITER //
DROP FUNCTION
IF EXISTS `ifempty`//

CREATE FUNCTION `ifempty`(
  s1 VARCHAR(4096),
  s2 VARCHAR(4096)
)
  RETURNS VARCHAR(4096)
  CHARSET utf8 NO SQL DETERMINISTIC SQL SECURITY INVOKER
  BEGIN

    IF (ISNULL(s1))
    THEN
      RETURN s2;
    ELSEIF CHAR_LENGTH(s1) = 0
      THEN
        RETURN s2;
    ELSE
      RETURN s1;
    END
    IF;
  END//

c) 存储过程示例

DELIMITER //

CREATE PROCEDURE test(IN b INT)

  BEGIN

    DECLARE a INT;

    SET a = b + 1;

    SELECT a;

  END

//

d）三种常用的循环写法 

while..do 写法 

DELIMITER //

CREATE PROCEDURE test(IN b INT)

  BEGIN

    DECLARE i INT;

    SET i = 0;

    WHILE i < b DO

      SELECT i;

      SET i = i + 1;

    END WHILE;

  END

//

repeat 写法 

DELIMITER //

CREATE PROCEDURE test(IN b INT)

  BEGIN

    DECLARE i INT DEFAULT 0;

    REPEAT

      SELECT i;

      SET i = i + 1;

    UNTIL i >= b

    END REPEAT;

  END

// 

loop .. end loop写法 

DELIMITER //

CREATE PROCEDURE test(IN b INT)

  BEGIN

    DECLARE i INT DEFAULT 0;

    mylabel: LOOP

      SELECT i;

      SET i = i + 1;

      IF i >= b
      THEN

        LEAVE mylabel;

      END IF;

    END LOOP;

  END

//

e) 游标示例 

DELIMITER //

CREATE PROCEDURE test(IN min_id INT)

  BEGIN

    DECLARE _done INT DEFAULT 0;
    -- 判断游标是否结束的标志 

    DECLARE p_id INT DEFAULT 0;

    DECLARE p_name VARCHAR(100) DEFAULT '';

    DECLARE _cur CURSOR FOR

      SELECT
        t.`d_id`,
        t.`d_name`
      FROM t_test AS t
      WHERE t.`d_id` >= min_id;

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;
    -- 标记循环结束 

    OPEN _cur;

    REPEAT

      FETCH _cur
      INTO p_id, p_name;

      IF NOT _done
      THEN

        SELECT
          p_id,
          p_name;

      END IF;

    UNTIL _done

    END REPEAT;

    CLOSE _cur;

  END

//

当然，也可以将游标的遍历换成while do ...end while的写法 

DELIMITER //

DROP PROCEDURE IF EXISTS p_test_cursor //

CREATE PROCEDURE p_test_cursor()

  BEGIN
    DECLARE _done INT DEFAULT 0; -- 判断游标是否结束的标志

    DECLARE p_activity_id INT DEFAULT 0;
    DECLARE p_community_id INT DEFAULT 0;

    DECLARE _cur CURSOR FOR
      SELECT
        t.activity_id,
        t.community_id
      FROM h_activity_community AS t;

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1; -- 标记循环结束

    OPEN _cur;

    WHILE _done != 1
    DO
      FETCH _cur
      INTO p_activity_id, p_community_id;

      IF (_done != 1) -- 如果游标没结束,就打印出这些变量值
      THEN
        SELECT
          p_activity_id,
          p_community_id,
          _done;
      END IF;

    END WHILE;
    
    CLOSE _cur;
    COMMIT;
  END
//

注：mysql的游标是以临时表实现的，性能不怎么样，如果游标中处理上十万条数据，就比较慢。