mysql 學習筆記 - yjmyzz－IT工程師數位筆記本

文章出處

以前主要使用oracle做數據庫，現在換成mysql了，發現不一樣的地方還是挺多的，記錄一下：

一、centos上的yum install方式安裝

完全卸載(可選，如果之前安裝了舊版本)

a) rpm -qa|grep mysql

先查看是否已經安裝了mysql

b) yum remove mysql*

執行完成后，再執行下a)中的命令確認下

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

參考配置：

 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

View Code

這里有幾個關鍵參數，需要修改

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 table1
2 INNER JOIN table2 ON (
3      table1.id = table2.id
4 )
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//

View Code

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 //

View Code

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 //

View Code

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 //

View Code

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 //

View Code

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 //

View Code

當然，也可以將游標的遍歷換成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 //