MySQL 常用操作

一、MySQL 資料庫存儲過程調用

delimiter //;
create procedure testP ()
begin
declare i int;
set i=0;
while i<5000 do
insert into person values (null, i+10);
set i=i+1;
commit;
end while;
end//
delimiter ;//
# 如下調用
call testP

• 因為存儲過程中每個語句是用；分開的，所以使用delimiter // 暫時將SQL分隔符便為//
• 註意testP後面要有空格再加上括弧

二、sync_binlog 的性能影響

1、預設sync_binlog=0 用MySQL自行決定何時同步binlog日誌到磁碟中

2、設置sync_binlog=1表示每一次binlog更新都同步到磁碟中

3、設置sync_binlog=100 表示每100次binlog日誌再同步到磁碟中

總結：

1. sync_binlog的設置若設置比較大，則可能在伺服器崩潰後，啟動時，會出現數據不一致問題，binlog日誌未更新到磁碟，
2. 如上可以看到，在頻繁的進行dml時，若sync_binlog設置過小則會嚴重影響MySQL的性能

三、跨資料庫導入

需求如下

SELECT seewo_sys_users.resourceid AS c_user_uid, 
    '' AS c_app_code,
    case when ISNULL(seewo_sys_users.unitId) then 0 else seewo_sys_users.unitId end AS c_unit_id, 
    case when ISNULL(seewo_teacher_subject_rel.stageId) then '' else seewo_teacher_subject_rel.stageId end AS c_stage_id, 
    case when ISNULL(seewo_teacher_subject_rel.subjectId) then '' else seewo_teacher_subject_rel.subjectId end AS c_subject_id,
    case when ISNULL(seewo_sys_users.realName) then 0 else seewo_sys_users.gender end AS c_realname, 
    case when ISNULL(seewo_sys_users.provinceId) then '' else seewo_sys_users.provinceId end AS c_province_id, 
    case when ISNULL(seewo_sys_users.cityId) then '' else seewo_sys_users.cityId end AS c_city_id, 
    case when ISNULL(seewo_sys_users.gender) then 0 else seewo_sys_users.gender end AS c_gender, 
    case when ISNULL(seewo_sys_users.phone) then '' else seewo_sys_users.phone end AS c_phone, 
    case when ISNULL(seewo_sys_users.cnname) then '' else seewo_sys_users.cnname end AS c_nickname, 
    seewo_sys_users.address AS c_address, 
    seewo_sys_users.photoURL AS c_photo_url, 
    seewo_sys_users.idNumber AS c_id_number, 
    case when ISNULL(seewo_sys_users.isDeleted) then 0 else seewo_sys_users.isDeleted end AS c_is_deleted, 
    NOW() as c_create_time,
    NOW() as c_update_time
FROM seewo_sys_users Left JOIN seewo_teacher_subject_rel ON seewo_sys_users.resourceid = seewo_teacher_subject_rel.teacherId

將執行結果導出後導入到encloud.t_seewo_user_info_sync 新增的表

方法一

1. 使用HeidiSQL 將查詢結果導出為SQL語句（insert 語句）
2. 在服務端直接使用source *.sql 導入數據

方法二

1. 使用navicat 將查詢結果導出到excel表中（帶列名導出）
2. 使用navicat導入到指定的資料庫表中

四、Binlog日誌分析

mysqlbinlog  --start-datetime='2017-07-19 16:30:00' --stop-datetime='2017-07-19 17:25:00' --database=seewo_school -vv --base64-output=decode-rows bin-log-mysqld.000010 > seewo_school.sql