MySQL 學習筆記

# 前言

如果可以過目不忘, 那可能就不用寫 Blog 了…


# 未開始先談移除

  1. 打開 terminal

  2. 使用 mysqldump 備份你的資料庫

    mysqldump -u你的使用者名 -p --all-databases > /tmp/backup.sql
  3. 尋找是否還有 MySQL process, 若有的話, 使用 kill -9 processId 砍掉

    ps -ax | grep mysql
  4. 若使用 brew, 使用 brew 刪除

    brew remove mysql
    brew cleanup
  5. 移除以下檔案

    sudo rm /usr/local/mysql
    sudo rm -rf /usr/local/var/mysql
    sudo rm -rf /usr/local/mysql*
    sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
    sudo rm -rf /Library/StartupItems/MySQLCOM
    sudo rm -rf /Library/PreferencePanes/My*
  6. 卸載之前的 MySQL 自動登入

    launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
  7. 移除之前的 MySQL 設定

    vim /etc/hostconfig
    # Remove the line MYSQLCOM=-YES-
  8. 移除以下檔案

    rm -rf ~/Library/PreferencePanes/My*
    sudo rm -rf /Library/Receipts/mysql*
    sudo rm -rf /Library/Receipts/MySQL*
    sudo rm -rf /private/var/db/receipts/*mysql*
  9. 重啟電腦, 確保沒有任何程序在跑

  10. 執行 mysql, 應該不能執行


# 使用者帳號及權限

建立使用者
CREATE USER 'userName'@'userHost'
IDENTIFIED BY 'userPassword';
更改使用者名稱
RENAME USER 'userName'@'hostName' TO 'newUserName'@'newHostName'
更改使用者密碼
SET PASSWORD FOR 'userName'@'hostName' = 'newPassword'
顯示特定使用者權限
SHOW GRANTS FOR 'userName'
賦予已建立的使用者權限
  • SQL 敘述:

    GRANT ALL ON 'databaseName'.'tableName' TO 'userName'@'hostName'
    GRANT SELECT (columnName1, columnName2, ...) ON 'databaseName'.'tableName' TO 'userName'@'hostName'
  • 備註:
    需注意, 在 MySQL 8, username 加上 hostName 視為一個完整的 GRANT 對象, 如果 hostName 不同, 即使 userName 相同, 對 GRANT 來說也是不同對象

顯示所有 user 以及 host
SELECT User, Host
FROM mysql.user
WHERE User LIKE 'userName';
刪除使用者
  • SQL 敘述:

    DROP USER 'userName'@'hostName';
  • 備註:
    需注意, userName 加上 hostName 視為一個完整 user 對象, 若有多個 host, 需下多次指令刪除

    賦予權限
    GRANT SELECT, INSERT, UPDATE, DELETE ON databaseName.tableName
    TO 'userName'@'hostName';
權限表
權限 說明
ALL [PRIVILEGES] 一次賦予所有基本權限。不需附加 GRANT 的各種選項。
ALTER (變換) 允許使用 ALTER TABLE 敘述, 但授權時亦須同時賦予 CREATE 和 INSERT 等權限。 如需替資料表更名時, 還要搭配 DROP 權限。 此權限有一定的風險: 某人可能透過更名資料表來倒取使用權
ALTER ROUTINE 允許使用者帳號變換或棄置 (DROP) 預儲常式 (STORED ROUTINES)。 這其中包括了 ALTER FUNCTION 和 ALTER PROCEDURES 兩道敘述。
CREATE 允許執行 CREATE TABLE 敘述。 注意還需要有 INDEX 權限才能定義索引。
CREATE TEMPORARY TABLES 允許使用 CREATE TEMPORARY TABLES 敘述
CREATE USER 允許使用者帳號執行數種與管理使用帳號相關之敘述: 如 CREATE USER 、 RENAME USER 、 REVOKE ALL PRIVILEGES 、 以及 DROP USER 等敘述。
CREATE VIEW 允許執行 CREATE VIEW 敘述
DELETE 允許執行 DELETE 敘述
DROP 允許使用者執行 DROP TABLE 和 TRUNCATE 兩道敘述
EVENT 允許使用者帳號為事件排程工具定義事件。 包括 CREATE EVENT, ALTER EVENT 和 DROP EVENT 等敘述
EXECUTE 允許以 EXECUTE 敘述執行預報程序 (STORED PROCEDURES)
FILE 允許使用 SELECT … INTO FILE 和 LOAD DATA INFILE 等敘述以便將資料會出成檔案, 或是從檔案匯入。 此權限也暗藏安全風險。 應該限定僅可在 SECURE_FILE_PRIV 變數中指定的特定目錄下執行
INDEX 授權使用 CREATE INDEX 和 DROP INDEX 等敘述
INSERT 允許執行 INSERT 敘述。 要先擁有此一權限才能執行 ANALYZE TABLE, OPTIMIZE TABLE 和 REPAIR TABLE 等敘述
LOCK TABLES 允許使用者對已有 SELECT 權限的資料表執行 LOCK TABLES 權限
PROCESS 允許使用 SHOW PROCESSLIST 和 SHOW ENGINE 等敘述
RELOAD 允許下達 FLUSH 敘述
REPLICATION CLIENT 允許使用者查詢主副伺服器 (MASTER AND SLAVE) 的狀態資訊, 如 SHOW MASTER STATUS 和 SHOW SLAVE STATUS 以及 SHOW BINARY LOGS 等敘述
REPLICATION SLAVE 這是複寫副伺服器 (REPLICATION SLAVE SERVER) 所必需的權限, 目的是要從主伺服器讀取二進位事件紀錄 (BINARY LOG EVENTS)
SELECT 允許執行 SELECT 敘述
SHOW DATABASES 允許對所有資料庫使用 SHOW DATABASES 敘述, 而且不限於使用者有權使用的資料庫
SHOW VIEW 允許使用 SHOW CREATE VIEW 敘述
SHUTDOWN 允許 MYSQLADMIN 工具程式搭配 SHUTDOWN 選項執行
SUPER 授權執行 CHANGE MASTER TO, KILL, PURGE BINARY LOGS 及 SET GLOBAL 等敘述, 並且允許 MYSQLADMIN 工具程式搭配 DEBUG 選項執行
TRIGGER 此權限授權使用者帳號得以建立或棄置觸發器 (TRIGGERS), 亦即可以使用 CREATE TRIGGER 和 DROP TRIGGER 等敘述
UPDATE 允許執行 UPDATE 敘述
USAGE 新建使用者時引用此關鍵字, 可達到完全不賦予權限, 或是在修訂現有使用者資料時不會動到既有權限的效果
建立賦予權限的帳號
GRANT specifiedPrivileges ON databaseName.tableName
TO 'userName'@'hostName'
IDENTIFIED BY 'userPassword'
WITH GRANT OPTION;
收回已賦予的權限
REVOKE specifiedPrivileges
ON databaseName.tableName
FROM 'userName'@'hostName'
建立 Role
CREATE ROLE 'roleName';

然後可以賦予 role 權限

賦予使用者 Role 權限
GRANT 'roleName' TO 'userName'@'hostName';
切換 Role
## 切換
SET ROLE 'roleName';

## 執行該 role 才擁有的權限
LOAD DATA INFILE
...

## 執行完畢後, 登出 Role
SET ROLE NONE;

# 資料備份

備份所有資料庫
mysqldump --user=specifiedUserAndHostName \
--password --lock-all-tables
--all-databases > /preferredLocationAndFileName.sql
備份指定資料庫
mysqldump --user=specifiedUserAndHostName --password --lock-tables \
--verbose --databases specifiedDatabaseName1 specifiedDatabaseName2 specifiedDatabaseNameN > specifiedBackupFileName.sql
備份參數參考

使用以下選項, 可以縮小 dump 檔
–skip-add-drop-table: 忽略 會清除舊資料表的 DROP TABLE 敘述
–skip-add-locks: 開始備份時不先鎖定資料表
–skip-comments: 檔案裡不加註解
–skip-disable-keys: 略過 會處理資料表索引的指令
–skip-set-charset: 略過 指定使用字元集的 SET NAMES 敘述
–compact: 使用上述所有選項

–user: 要求 mysqlsump 使用 admin_backup 這個帳號與 MySQL Server 互動
–password: 指定使用帳號的密碼
–lock-all-tables: 在開始備份前, 把所有資料庫中的所有資料表鎖定
–lock-tables: 在開始備份前, 先把該資料庫中所有資料表鎖定
–all-databases: 指定匯出所有資料庫
–extended-insert: 會把每個資料表所需的多筆 INSERT 敘述濃縮成一句, 可縮小備份檔以及增進回原效率
–skip-extended-insert: 若 server 預設有 --extended-insert 效果, 但想看到一筆筆的 INSERT 敘述, 可以使用此選項
–ignore-table: 略過指定 table, 通常會略過 mysql.user table, 該 table 會用特殊帳號備份
–no-create-info: 不包含 create 敘述
–no-data: 只備份架構, 不備份資料
–verbose: 在備份過程中, 把重大步驟產生的訊息顯示出來
–replace: 使用 replace 代替 insert


# 資料還原

# dump file 還原

從 dump file 還原
mysql --user=performingOperationUser --password  < dumpFileName.sql
復原部分資料
  1. 修改 dump file, 只留下開頭, 結尾變數, 以及需要的 table 段落
  2. 修改 dump file 中的 create database, use database, 改成一個臨時的 database name, 匯出後在從該臨時 database 中取出資料, 完成後再刪掉該 temporary database
  3. 或是賦予一個臨時使用者該 table 的權限, 讓這個使用者去執行 restore

# 從 Binary Log 還原

如果很不幸的, 你想要還原備份後才新增的資料

確認 Binary Log 是否有啟用
SHOW BINARY LOGS

# 如果未啟用, 錯誤如下:
# ERROR 1381 (HY000): You are not using binary logging
啟用 binary log

在 mysql.ini / mysql.cnf 檔案中:

# log-bin 表示啟動 binary log
log-bin
# 表示不 log mysql 這個 table, 以提高安全性
binlog-ignore-db=mysql
顯示當前 log 點
SHOW MASTER STATUS;

Binlog_Do_DB: 指定哪些資料庫才要記錄到 binary log
Binlog_Ignore_DB: 指定哪些資料庫不要記錄到 binary log
Executed_Gtid_Set: 已經執行的 transaction 編號

取得 DATA 存在位置
SHOW VARIABLES WHERE Variable_Name LIKE 'datadir';

確定 log 檔案確實在該目錄底下後, 輸出文字檔
mysqlbinlog --database=databaseName \
/binaryLogAbsoluteLocation > whateverNameYouLike.txt
找出問題點

手動找出問題點, 如下 example

# at 1258707
#140916 13:10:24 server id 1 end_log_pos 1258778
Query thread_id=382 exec_time=0 error_code=0
SET TIMESTAMP=1410887424/*!*/;
SET @@session.sql_mode=0/*!*/;

BEGIN
/*!*/;

# at 1258778
#140916 13:10:24 server id 1 end_log_pos 1258900
Query thread_id=382 exec_time=0 error_code=0
use `rookery`/*!*/;
SET TIMESTAMP=1410887424/*!*/;

DELETE FROM birds_simple WHERE common_name LIKE '%Blue%'
/*!*/;

# at 1258900
#140916 13:10:24 server id 1 end_log_pos 1258927 Xid = 45248

COMMIT/*!*/;

...

# at 1284668
#140916 13:10:28 server id 1 end_log_pos 1284739
Query thread_id=382 exec_time=0 error_code=0
SET TIMESTAMP=1410887428/*!*/;
SET @@session.sql_mode=0/*!*/;
BEGIN
/*!*/;

# at 1284739
#140916 13:10:28 server id 1 end_log_pos 1284862
Query thread_id=382 exec_time=0 error_code=0
SET TIMESTAMP=1410887428/*!*/;
DELETE FROM birds_simple WHERE common_name LIKE '%Green%'
/*!*/;

# at 1284862
#140916 13:10:28 server id 1 end_log_pos 1284889 Xid = 45553
COMMIT/*!*/;
復原備份到問題點前

可看到從 position point 1258707 開始了含有 DELETE 的 transaction, 現在我們要將資料復原, 並拿掉這個含有 DELETE 的 transaction
先復原到 position 1258707:

mysqlbinlog --database=databaseName--stop-position="1258707" \
binaryLogAbsoluteLocation |
mysql --user=userName --password
從問題點後開始復原到最新的 log point

可看到有問題的 transaction 的結尾落在 1284862, 並且有指出下一個起始點為 1284889, 因此可從 position 1284889 開始往後復原

mysqlbinlog --database=rookery  --start-position="1284889" --to-last-log \
binaryLogAbsoluteLocation |
mysql --user=userName --password

至此就大功告成啦!


# 中文亂碼問題

# 先確認 locale 狀態:

  1. 確認 charset 狀態, 在 mysql 當中:

    show variables like 'char%';

  2. 確認 database locale 狀態:

    SELECT default_character_set_name FROM information_schema.SCHEMATA 
    WHERE schema_name = "databaseName";
  3. 確認 table locale 狀態:

    SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
    information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
    WHERE CCSA.collation_name = T.table_collation
    AND T.table_schema = "databaseName"
    AND T.table_name = "tableName";
  4. 確認 column locale 狀態:

    show full columns from tableName;
    ```
    5. 確認 `collation`
    ```bash
    show variables like 'collation%';

# locale 修改

# 確認 mysql 設定檔位置並修改

  • 取得 ‘my.cnf’ 位置, 通常,檔案會在 /etc/mysql/my.cnf

    mysql --help -verbose | grep 'my.cnf'
  • 編輯檔案

    vim /etc/mysql/my.cnf
  • 貼上以下設定

    [client]
    default-character-set=utf8mb4

    [mysql]
    default-character-set=utf8mb4


    [mysqld]
    collation-server = utf8_unicode_ci
    init-connect='SET NAMES utf8'
    character-set-server = utf8mb4

# 修改 database locale

ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci;

# 修改 table locale

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;

# 完成修改後, 重啟 mysql

service mysql restart

# 別忘了 PHP locale

如果有用使用 PHP 的話,記得也將 PHP 那邊的 locale 設為 uft8, 可參考文章

mysqli_set_charset($dbc,"utf8");

# 還是亂碼?

理論上,上面的都做完了,應該就不會有亂碼了,如果問題尚未解決,可以試試下面的方法

  • 查看資料庫 locale 設定

    show create database databaseName
  • 查看 table locale 設定

    show create table tableName
  • 其餘設定

    set names uft8;

# 匯入大量資料

# 修改 my.cnf 或 my.ini 檔 (永久性的放寬)

[mysqld]
max_allowed_packet=100M

# 暫時性的放寬

set global net_buffer_length=1000000;
set global max_allowed_packet=1000000000;

# 匯入

# 從 sql 檔匯入

mysql --max_allowed_packet=100M -u root -p database < dump.sql

如果已有設定, --max_allowed_packet 可不加

# 從 csv 匯入

# 一般 csv 格式
LOAD DATA INFILE '/tmp/Clements-Checklist-6.9-final.csv'
INTO TABLE rookery.clements_list_import
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES;
(id, change_type, @niente, @niente,
scientific_name, english_name,
@niente, bird_order, @family, @niente,
@niente, @niente, @niente, @niente,
@niente, @niente, @niente, @niente);
SET family = SUBSTRING(@family, 1, LOCATE(' (', @family) );

從 csv 檔讀取資料並匯入 database.table
FIELD TERMINATED BY: field 跟 field 之間由 , 區隔開來
OPTIONALLY: 有則處理, 沒有則不執行
ENCLOSED BY: 使用 ENCLOSED BY '"', 當 " (doube quote) 有出現時, 會將兩個 " (double quote) 之間的內容視為一個 column 的內容, 若沒出現則不使用, 為了應付某些 filed 的內容其實是 text, 會用 " (double quote) 包住, 但內容有許多 ,(comma)
IGNORE 1 LINES: 表示忽略第一行, 因為第一行是 field name, 我們並不會用到
@niente: 因為 csv 檔中某些欄位的資料我們並不需要, 因此在建立 table 時只需要建立我們需要的 column, 在匯入時, 將沒用到的 fieldd 按照 csv 上的順序標示為 @niente (只要是變數就行, 名稱不重要), 這樣就不會將資料匯入啦
@family, SET family: 可以在 LOAD DATA 的過程中, 針對 csv 上特定的 column 做處理, 匯入完成後就已經會是處理好的

# 較特別的 csv 格式

格式範例:

["prospect name"|"prospect email"|"prospect country"]
["Mr. Bogdan Kecman"|"bodgan\@kecman-birds.com"|"Serbia"]
["Ms. Sveta Smirnova"|"bettasveta\@gmail.com"|"Russia"]
["Mr. Collin Charles"|"callincollin\@gmail.com"|"Malaysia"]
["Ms. Sveta A. Smirnova"|"bettasveta\@gmail.com"|"Russia"]

sql example:

LOAD DATA INFILE '/tmp/birdwatcher-prospects.csv'
INTO TABLE birdwatchers.birdwatcher_prospects_import
FIELDS TERMINATED BY '|' ENCLOSED BY '"' ESCAPED BY '\\'
LINES STARTING BY '[' TERMINATED BY ']\r\n'
IGNORE 1 LINES
(prospect_name, prospect_email, prospect_country);

以下為 FIELDS 子句意思:
TERMINATED BY: 表示 | 為 field 跟 field 之間的間隔
ENCLOSED BY: 表示每一個 field 的內容都會由 " 包住
ESCAPE BY: 標示跳脫用字元, 不過預設就是 \, 因此這個可省略

LINES 子句:
STRINGS BY: 表示 [ 開頭算一行的開始
TERMINATED BY: 表示 ]\r\n 為一行的結束, 正常 Linux 只需 \n 即可, 但考量到 Windows 環境, 因此多加了 \r

使用 mysqlimport
mysqlimport –user='marie_dyer' --password='sevenangels' \
--replace --low-priority --ignore-lines='1' \
--fields-enclosed-by='"' --fields-terminated-by='|' --fields-escaped-by='\\' \
--lines-terminated-by=']\r\n' \
--columns='prospect_name, prospect_email, prospect_country' \
birdwatchers '/tmp/birdwatcher_prospects_import.csv'

基本上語法跟 LOAD DATA 是一樣的, 差別在於檔名若有 - 要改成 _, 以免 MySQL 判定為刪減符號

以下為 FIELDS 子句意思:
TERMINATED BY: 表示 | 為 field 跟 field 之間的間隔
ENCLOSED BY: 表示每一個 field 的內容都會由 " 包住
ESCAPE BY: 標示跳脫用字元, 不過預設就是 \, 因此這個可省略

LINES 子句:
STRINGS BY: 表示 [ 開頭算一行的開始
TERMINATED BY: 表示 ]\r\n 為一行的結束, 正常 Linux 只需 \n 即可, 但考量到 Windows 環境, 因此多加了 \r


# 大量匯出

( SELECT 'scientific name','common name','family name' )
UNION
( SELECT birds.scientific_name,
IFNULL(common_name, ''),
bird_families.scientific_name
FROM rookery.birds
JOIN rookery.bird_families USING(family_id)
JOIN rookery.bird_orders USING(order_id)
WHERE bird_orders.scientific_name = 'Charadriiformes'
ORDER BY common_name
INTO OUTFILE '/tmp/birds-list.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY '|' ESCAPED BY '\\'
LINES TERMINATED BY '\n');

使用 SELECT INTO OUTFILE 將資料匯出, 子句部分語法跟 LOAD DATA 完全一樣
需使用 IFNULL 將 null 轉為空字串, 因為 INTO OUTFILE 預設會將 null 轉為 n

以下為 FIELDS 子句意思:
TERMINATED BY: 表示 | 為 field 跟 field 之間的間隔
ENCLOSED BY: 表示每一個 field 的內容都會由 " 包住
ESCAPE BY: 標示跳脫用字元, 不過預設就是 \, 因此這個可省略

LINES 子句:
STRINGS BY: 表示 [ 開頭算一行的開始
TERMINATED BY: 表示 ]\r\n 為一行的結束, 正常 Linux 只需 \n 即可, 但考量到 Windows 環境, 因此多加了 \r

匯出範例:

"scientific name"|"common name"|"family name"
"Charadrius vociferus"|"Killdeer"|"Charadriidae"
"Charadrius montanus"|"Mountain Plover"|"Charadriidae"
"Charadrius alexandrinus"|"Snowy Plover"|"Charadriidae"
"Pluvialis squatarola"|"Black-bellied Plover"|"Charadriidae"
"Pluvialis fulva"|"Pacific Golden Plover"|"Charadriidae"
"Burhinus vermiculatus"|"Water Thick-knee"|"Burhinidae"
"Burhinus oedicnemus"|"Eurasian Thick-knee"|"Burhinidae"
...

# 查單一資料庫 size

SELECT table_schema "databaseName", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables where table_schema="databaseName" GROUP BY table_schema;

# where 子句操作符


# EXTRACT 支援的時間格式


# DATE_FORMAT, TIME_FORMAT 時間格式碼


# Index

# 加入 Foreign Key

ALTER TABLE tableName
ADD FOREIGN KEY (columnName) REFERENCES referencingTableName (columnName)

# 移除 Foreign Key

# 取得 constraint

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='tableName';

# 移除 constraint

ALTER TABLE table_name 
DROP CONSTRAINT constraint_name

# 移除 index

ALTER TABLE table_name 
DROP INDEX index_name

# 取得 metadata

取得 table rows 大約估計值
SELECT table_rows FROM information_schema.tables WHERE table_name = 'tableName'

# Questions and Answers

MySQL 中, binlog 的 row 模式, 是用什麼方式紀錄 log?

紀錄 row 的內容, 記兩條, 更新前和更新後

MySQL 中, binlog 的 statement 模式, 是用什麼方式紀錄 log?

紀錄 sql 語句

MySQL 中, binlog 又分為哪兩種模式?

statement
row

MySQL 中, redo log 是物理性, binlog 是邏輯性, 何謂物理性跟邏輯性?

redo log 紀錄硬碟上數據的物理變化
binlog 紀錄當時所執行的 sql 語法

MySQL 中, innodb_flush_log_at_trx_commit=1 這個參數的意思是?

讓每次 redo log 都會持久化到 disk

MySQL 中, 又不是常常會需要復原資料, 為何一定要使用二階段提交來確保 redo log 與 binlog 資料一致?

因為當需要 scale 來增加系統 read 的 capacity 時, 便會用到 binlog 來實現增加新的 read database, 若資料不一致便無法實現這樣的操作

MySQL 中, 假設不使用二階段提交, 先寫 binlog, 再寫 redo log, 假設 binlog 寫完, 但 binlog 還沒寫完時就 crash 了, 會發生什麼事?

由於 redo log 還沒寫, 也就是該 transaction 無效, 但 binlog 已寫了, 造成跟原庫的資料不一致

MySQL 中, 假設不使用二階段提交, 先寫 redo log, 再寫 binlog, 假設 redo log 寫完, 但 binlog 還沒寫完時就 crash 了, 會發生什麼事?

server 重啟後, 因為 redo log 的 crash save 能力, 資料並沒有丟失, 但是如果使用 binlog 復原資料時, 便會少了那筆紀錄, 造成兩邊資料不一致

MySQL 中, redo log 與 binlog 的二階段提交主要的作用是?

讓兩份 log 的邏輯一致

MySQL 中, binlog 跟 redo log 分別的主要作用是?
  • redo log: crash save (innoDB 層面)
  • binlog: 恢復某個時間點的數據
MySQL 中, redo log 與 binlog 的寫入流程是?

  1. 執行器先找引擎取 ID=2 這一行。 ID 是主鍵,引擎直接用樹搜索找到這一行。如果 ID=2 這一行所在的數據頁本來就在內存中,就直接返回給執行器;否則,需要先從磁盤讀入內存,然後再返回。
  2. 執行器拿到引擎給的行數據,把這個值加上 1,比如原來是 N,現在就是 N+1,得到新的一行數據,再調用引擎接口寫入這行新數據。
  3. 引擎將這行新數據更新到內存中,同時將這個更新操作記錄到 redo log 裡面,此時 redo log 處於 prepare 狀態。然後告知執行器執行完成了,隨時可以提交事務。
  4. 執行器生成這個操作的 binlog,並把 binlog 寫入磁盤。
  5. 執行器調用引擎的提交事務接口,引擎把剛剛寫入的 redo log 改成提交(commit)狀態,更新完成。
    重點: binlog 與 redo log 會有共同的 XID, 當 crash 重啟後, 如果有 prepare 又有 commit 的 redo log, 就直接提交, 如果有 prepare 但沒有 commit 的 redo log, 就拿著 XID 去尋找是否有該 binlog, 有則提交, 無則 rollback
MySQL 中, redo log 和 binlog, 哪個是循環寫 哪個是追加寫?

redo log 是循環寫, binlog 是追加寫

MySQL 中, redo log 和 binlog, 哪個是物理性, 哪個是邏輯性?

redo log 是物理性, binlog 是邏輯性

MySQL redo log 中, 先寫日誌, 具體來說是寫到哪? 跟實際更新硬碟的差別在於?

也是寫到磁盤中, 但如果跟實際更新硬碟相比, 一個是順序 I/O, 一個是隨機 I/O, 差別在於隨機 I/O 有一個尋址的過程, cost 較高

MySQL 中, 任何 Storage Engine 都可使用, 屬於 Server 層的 Log 是哪一個?

binlog

MySQL 中, redo log 是屬於哪種儲存引擎專屬的?

InnoDB

MySQL 整體上來看, 其實就分為哪兩層?

server, storage

以下的 MySQL redo log picture 中, check point 代表的意思是?
  • Example
  • Answer
    當前要擦除的位置
以下的 MySQL redo log picture 中, write pos 代表的意思是?
  • Example
  • Answer
    目前從此處開始寫入
以下的 MySQL redo log picture 中, 綠色部分代表的意思是?
  • Example
  • Answer
    可以寫入的空白地方
MySQL 的 redo log 主要解決了什麼問題?

減少 I/O 成本
如果每一次的更新操作都需要寫進磁盤,然後磁盤也要找到對應的那條記錄,然後再更新,整個過程 IO 成本、查找成本都很高

MySQL 的 redo log 可以讓 MySQL server 就算臨時異常重啟, 之前提交的記錄也不會丟失, 這種能力稱為?

crash-save
關鍵點就是先寫日誌,再寫磁盤,也就是先寫粉板,等不忙的時候再寫賬本。

以下的 MySQL picture 是哪一種 log?
  • Example
  • Answer
    redo log
MySQL 中, redo log 的大小是固定的, 對嗎?

對的

MySQL 中, WAL 的全稱是?

Write-Ahead Logging, 先寫日誌, 再寫磁盤

MySQL 8.0 還能使用查詢緩存嗎?

不行

MySQL 查詢緩存, 什麼是 key, 什麼是 value?

查詢語句為 key, 查詢結果為 value

MySQL 中, 預設的 client 端與 server 端的連線是多久?

8 小時

以下的 MySQL example picture 的意思是?
  • Example
  • Answer
    MySQL 的架構圖
MySQL 的 timestamp column, 從資料庫看, 顯示的時間是哪一個時區?

預設為機器當下的時區, 因為 MySQL timezone variable 預設去抓 system timezone

MySQL 的 timestamp column 會隨著系統的時區調整而變動, 為什麼?

MySQL timestamp 會在底層儲存 UTC 的時間, 而從資料庫中看到的時間是根據當下機器的時區轉化過的, 所以 timestamp 欄位會隨著機器的時區不同而顯示該時區的時間, 所以調整 MySQL 的 timezone 是不會影響到資料準確度的

以下的 MySQL example code 的意思是?
  • Example

    SELECT
    `branch_managers`.`account`,
    `branch_managers`.`created_at`,
    `shops`.`name`
    FROM
    `branch_managers`
    INNER JOIN `shops` ON `branch_managers`.`shop_id` = `shops`.`id`
    WHERE
    AND `branch_managers`.`id` in(
    SELECT
    `id` FROM ((
    SELECT
    `id` FROM `branch_managers`
    WHERE
    match(account) against ('4k' IN boolean mode))
    UNION (
    SELECT
    `branch_managers`.`id` FROM `branch_managers`
    INNER JOIN `shops` ON `branch_managers`.`shop_id` = `shops`.`id`
    WHERE
    match(name) against ('4k' IN boolean mode))) AS `matches`)
  • Answer
    無法直接使用 or where match 這樣子的 full text search 在多個表格, 因此統一取得 id, 在使用 where in 取得最後需要的資料
    使用 union 來取得兩個 select query 取得的不重複 id
    使用 derived table, 避免 where in 與 union query 之間的 dependency

以下的 MySQL example image 的意思是?
  • Example
  • Answer
    SELECT 時加上 exclusive lock, 雖然 weight column 並沒有 index, 但 MySQL 為了做到 RR Isolation, 會把整張 table lock 住
使用 MySQL 的 range lock 時, 要特別注意什麼事?

range column 一定要有 index, 否則 MySQL 為了維持 RR Isolation, 會 lock 整張 table

MySQL 的 range lock 如果 range column 並沒有 index, 那會有什麼行為?

MySQL 為了維持 RR Isolation, 會 lock 整張 table

以下的 MySQL example image 的意思是?
  • Example
  • Answer
    SELECT 時加上 exclusive lock, 所以所有 height >= 170 這個 range 的 row 都被鎖住了
MySQL 的 repeatable isolation 中, 如何避免 lost update?

使用 Atomic operation, 像是 credit = credit - 1, 但如果要避免負數, 還是要使用 lock

MySQL 的 repeatable isolation, 實作的 snapshot 只對什麼語法有效?

只對 DQL, 像是 select 有效, 對 DML, 像是 insert, update, delete 都無效

MySQL 的 repeatable isolation 是如何實作 snapshot 的?

會在 transaction 一開始進行一次 select, 並記錄下時間, 之後在此 transaction 中只可看到此時間之前的 committed row, 已經 transaction 自己本身作出的改變, 但這個規則只對 DQL (Data Query Language)有效, 對 DML (Data Manipulation Language) 無效

以下的 MySQL transaction illustration image 的意思是?
  • Example
  • Answer
    MySQL 的 repeatable read 只可避免 phantom read, 但無法避免 phantom update, insert, delete 等其他操作
    在 transaction 中, update 的作用範圍依然是所有已經 commit 的 row, 而在 transaction 中, 可以看到自己 update 的 row, 因此 update 造成了 write skew, 而 select 產生 phantom read
資料庫中, Serializable Isolation 中的 Serializable Snapshot Isolation, 是如何實作?

不特別對 transaction 做互相 block, 而是在最後 commit 的時候再做確認, 效能最佳, 但 2008 年才提出, 因此大部分資料庫都還沒有採用

大部分的資料庫都採用哪種方式實作 Serializable Isolation?

Two-phase Lock

資料庫中, Serializable Isolation 中的 Two-phase Lock, 是如何實作?

使用 Shared Lock 以及 Exclusive Lock 配合, 讓每一筆資料在同一時間最多都只會有一個 Transaction 對它進行讀寫, 還要搭配 Range Lock 避免 Phantom 現象, 大部分資料庫都採用這種實作

資料庫中, Serializable Isolation 中的 Serial Order, 是如何實作?

真正照順序進行資料讀寫, 避免所有同時執行更新可能造成的衝突, 犧牲 concurrency, 效能差, 適合讀寫較快速的 In-Memory 資料庫, 如 redis

資料庫中, Serializable Isolation 有哪三種實作方式?

Serial Order
Two-phase Lock
Serializable Snapshot Isolation

資料庫中, 何謂 Serializable Isolation?

可以保證在多個 Transaction 同時對資料庫進行讀寫所得到的結果, 會跟一次只讓一個 transaction 照順序 (serially) 進行讀寫所得到的結果完全一致, 最嚴格的 Isolation level, 但效能較差

資料庫中, 何謂 Repeatable Read Isolation?

只要可以避免 Dirty Read 和 Non-repeatable Read, 就可以稱為 Repeatable Read Isolation, 依照實作方法不同, 有些資料庫的 Repeatable Read 可以避免 Lost Update 以及 Phantom

資料庫中, 何謂 Read Committed Isolation?

只允許讀取已經被 Commit 的資料, 可以避免 dirty read

資料庫中, 何謂 Read Uncommitted Isolation?

允許讀取尚未被 commit 的資料

資料庫中, Isolation 又分為哪四種?

Read Uncommitted Isolation
Read Committed Isolation
Repeatable Read Isolation
Serializable Isolation

資料庫中, 以下的情況又稱為?
  • Example:
  • Answer:
    Write Skew
    只有 Serializable Isolation 可以避免
資料庫中, 以下的情況又稱為?
  • Example:
  • Answer:
    Phantom Read, Serializable Isolation 可以避免這個現象, Repeatable Read Isolation 視乎各個資料的實作, PostgreSQL 可以完全避免, MySQL InnoDB 只能避免 Phantom Read, 但無法避免 UPDATE, DELETE 等 DML 寫入的操作
資料庫中, 以下的情況又稱為?
  • Example:
  • Answer:
    Lost Update, Serializable Isolation 可以避免這個情況, Repeatable Read Isolation 看每個資料庫的實作不同有不同的行為, MySQL 無法避免 Lost Update, PostgreSQL 可以
資料庫中, 以下的情況又稱為?
  • Example:
  • Answer:
    Read Skew (Non-repeatable Read), Repeatable Read 或更高的 Isolation 可避免這個情況
資料庫中, 以下的情況又稱為?
  • Example:
  • Answer:
    Dirty Read, Read Committed 或更高的 Isolation 可避免 dirty read
以下的 MySQL example code 中, range lock 範圍內的資料 InnoDB 可以讀嗎?可以寫嗎?
  • Example:

    SELECT * FROM student WHERE height >= 170 FOR UPDATE;
  • Answer:
    如果 Isolation level 是 Serializable 的話, 不可讀不可寫
    如果 Isolation level 是 Repeatable Read 的話, 可讀不可寫, 因為 InnoDB 的 RR Isolation 實作是採用 Snapshot Isolation, 讀取時都是讀取 Snapshot 內的資料

以下的 MySQL example code 中, 哪些資料會被鎖住? 會加上什麼鎖?
  • Example:

    SELECT * FROM student WHERE height >= 170 FOR UPDATE;
  • Answer:
    height >= 170 的資料
    exclusive lock

MySQL 中, 如果一筆資料只被一個 transaction 加上 shared lock, 該 transaction 可以將此 shared lock 升級成 exclusive lock 嗎?

可以

MySQL 中, 當一筆資料被 1 個或 1 個以上的 transaction 加上 shared lock, 該筆資料可以被加上 exlusive lock 嗎?

不行

MySQL 中, 同一筆資料可以同時被多個 transaction 加上 shared lock 嗎?

可以

MySQL 中, 當一個 transaction 取得某資料的 shared lock, 在該 lock 釋放之前, 其他 transaction 可以對此資料進行寫入嗎?

不行

MySQL 中, 當一個 transaction 取得某資料的 shared lock, 在該 lock 釋放之前, 其他 transaction 可以對此資料進行讀取嗎?

可以

MySQL 中, 當一個 transaction 取得某資料的 exclusive lock, 在該 lock 釋放之前, 其他 transaction 可以對此資料進行讀取嗎?

不行

MySQL 中, 當一個 transaction 取得某資料的 exclusive lock, 在該 lock 釋放之前, 其他 transaction 可以對此資料進行寫入嗎?

不行

MySQL 中, 何謂 shared lock?

當一個 transaction 取得資料的 shared lock 之後, 其他 transaction 可以 讀取 資料, 但無法 寫入 資料

MySQL 中, 何謂 Exclusive lock?

當一個 transaction 取得資料的 exclusive lock 之後, 其他 transaction 無法對此資料做 讀取 以及 寫入
例如: select a from b for update

以下的 MySQL example code 的意思是?
  • Example:

    mysqlimport –user='marie_dyer' --password='sevenangels' \
    --replace --low-priority --ignore-lines='1' \
    --fields-enclosed-by='"' --fields-terminated-by='|' --fields-escaped-by='\\' \
    --lines-terminated-by=']\r\n' \
    --columns='prospect_name, prospect_email, prospect_country' \
    birdwatchers '/tmp/birdwatcher_prospects_import.csv'
  • Answer:
    基本上語法跟 LOAD DATA 是一樣的, 差別在於檔名若有 - 要改成 _, 以免 MySQL 判定為刪減符號
    以下為 FIELDS 子句意思:

TERMINATED BY: 表示 | 為 field 跟 field 之間的間隔
ENCLOSED BY: 表示每一個 field 的內容都會由 " 包住
ESCAPE BY: 標示跳脫用字元, 不過預設就是 \, 因此這個可省略
LINES 子句:
STRINGS BY: 表示 [ 開頭算一行的開始
TERMINATED BY: 表示 ]\r\n 為一行的結束, 正常 Linux 只需 \n 即可, 但考量到 Windows 環境, 因此多加了 \r

以下的 MySQL example code 的意思是?
  • Example:

    ( SELECT 'scientific name','common name','family name' )
    UNION
    ( SELECT birds.scientific_name,
    IFNULL(common_name, ''),
    bird_families.scientific_name
    FROM rookery.birds
    JOIN rookery.bird_families USING(family_id)
    JOIN rookery.bird_orders USING(order_id)
    WHERE bird_orders.scientific_name = 'Charadriiformes'
    ORDER BY common_name
    INTO OUTFILE '/tmp/birds-list.csv'
    FIELDS ENCLOSED BY '"' TERMINATED BY '|' ESCAPED BY '\\'
    LINES TERMINATED BY '\n');
  • Answer:
    使用 SELECT INTO OUTFILE 將資料匯出, 子句部分語法跟 LOAD DATA 完全一樣
    需使用 IFNULL 將 null 轉為空字串, 因為 INTO OUTFILE 預設會將 null 轉為 n
    以下為 FIELDS 子句意思:

TERMINATED BY: 表示 | 為 field 跟 field 之間的間隔
ENCLOSED BY: 表示每一個 field 的內容都會由 " 包住
ESCAPE BY: 標示跳脫用字元, 不過預設就是 \, 因此這個可省略
LINES 子句:
STRINGS BY: 表示 [ 開頭算一行的開始
TERMINATED BY: 表示 ]\r\n 為一行的結束, 正常 Linux 只需 \n 即可, 但考量到 Windows 環境, 因此多加了 \r

MySQL 中, 如果目標 server 並不支援 LOAD DATA INFILE, 那有什麼解法?

找一台有支援的使用 LOAD DATA 匯入後, 在匯出 mysqldump file, 再到目標 server 上復原 from mysqldump file

MySQL 中, 如果遠端登入使用 LOAD DATA 匯入檔案操作, 在 server 端跟 client 端都要開啟哪一個選項?

local-infile=1

以下的 MySQL example code 的意思是?
## csv 格式範例:
["prospect name"|"prospect email"|"prospect country"]
["Mr. Bogdan Kecman"|"bodgan\@kecman-birds.com"|"Serbia"]
["Ms. Sveta Smirnova"|"bettasveta\@gmail.com"|"Russia"]
["Mr. Collin Charles"|"callincollin\@gmail.com"|"Malaysia"]
["Ms. Sveta A. Smirnova"|"bettasveta\@gmail.com"|"Russia"]

## SQL 範例
LOAD DATA INFILE '/tmp/birdwatcher-prospects.csv'
INTO TABLE birdwatchers.birdwatcher_prospects_import
FIELDS TERMINATED BY '|' ENCLOSED BY '"' ESCAPED BY '\\'
LINES STARTING BY '[' TERMINATED BY ']\r\n'
IGNORE 1 LINES
(prospect_name, prospect_email, prospect_country);
  • Answer:
    從 csv 檔讀取資料並匯入 database.table

FIELD TERMINATED BY: field 跟 field 之間由 , 區隔開來
OPTIONALLY: 有則處理, 沒有則不執行
ENCLOSED BY: 使用 ENCLOSED BY '"', 當 " (doube quote) 有出現時, 會將兩個 " (double quote) 之間的內容視為一個 column 的內容, 若沒出現則不使用, 為了應付某些 filed 的內容其實是 text, 會用 " (double quote) 包住, 但內容有許多 ,(comma)
IGNORE 1 LINES: 表示忽略第一行, 因為第一行是 field name, 我們並不會用到
@niente: 因為 csv 檔中某些欄位的資料我們並不需要, 因此在建立 table 時只需要建立我們需要的 column, 在匯入時, 將沒用到的 fieldd 按照 csv 上的順序標示為 @niente (只要是變數就行, 名稱不重要), 這樣就不會將資料匯入啦
@family, SET family: 可以在 LOAD DATA 的過程中, 針對 csv 上特定的 column 做處理, 匯入完成後就已經會是處理好的

以下的 MySQL example code 的意思是?
  • Example:

    LOAD DATA INFILE '/tmp/Clements-Checklist-6.9-final.csv'
    INTO TABLE rookery.clements_list_import
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    IGNORE 1 LINES;
    (id, change_type, @niente, @niente,
    scientific_name, english_name,
    @niente, bird_order, @family, @niente,
    @niente, @niente, @niente, @niente,
    @niente, @niente, @niente, @niente);
    SET family = SUBSTRING(@family, 1, LOCATE(' (', @family) );
  • Answer:
    從 csv 檔讀取資料並匯入 database.table
    FIELD TERMINATED BY: field 跟 field 之間由 , 區隔開來
    OPTIONALLY: 有則處理, 沒有則不執行
    ENCLOSED BY: 使用 ENCLOSED BY '"', 當 " (doube quote) 有出現時, 會將兩個 " (double quote) 之間的內容視為一個 column 的內容, 若沒出現則不使用, 為了應付某些 filed 的內容其實是 text, 會用 " (double quote) 包住, 但內容有許多 ,(comma)
    IGNORE 1 LINES: 表示忽略第一行, 因為第一行是 field name, 我們並不會用到
    @niente: 因為 csv 檔中某些欄位的資料我們並不需要, 因此在建立 table 時只需要建立我們需要的 column, 在匯入時, 將沒用到的 fieldd 按照 csv 上的順序標示為 @niente (只要是變數就行, 名稱不重要), 這樣就不會將資料匯入啦
    @family, SET family: 可以在 LOAD DATA 的過程中, 針對 csv 上特定的 column 做處理, 匯入完成後就已經會是處理好的

使用 MySQL 時, 當遇到 warning 時, 如何顯示 warning?
SHOW WARNINGS;
以下的 MySQL example code 的意思是?
  • Example:

    LOAD DATA INFILE '/tmp/Clements-Checklist-6.9-final.csv'
    INTO TABLE rookery.clements_list_import
    FIELDS TERMINATED BY ',';
  • Answer:
    讀取 csv 檔並 insert 到指定 database.table, 以 , 為 filed 跟 field 之間的分隔點

MySQL 中, 當我在 CREATE TABLE 時, 如果 column name 可能是 MySQL 的保留字, 那我要用什麼符號將 column name 包住?

` ` (backquote) 符號

MySQL 中, 若要執行 LOCK TABLE, 需先把哪一個選項關閉, 以避免 LOCK TABLE 與 TRANSACTION 相互影響?

AUTOCOMMIT

MySQL 中, 目前 innoDB 是如何處理死鎖的?

將持有最少行級排他鎖的 transaction rollback

以下的 MySQL example code 中, 當兩個 transaction 同時執行時, 很可能會產生哪一種問題?
  • Example:

    事務1
    START TRANSACTION;
    UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
    UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';
    COMMIT;
    事務2
    START TRANSACTION;
    UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
    UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = '2002-05-01';
    COMMIT;
  • Answer:
    死鎖, 即事務 1, 2 同時執行了第一條 update, 這時第一句 update 作用的 row 已被鎖住, 而當事務 1, 2 嘗試執行第二句 update 時, 發現該 row 已被鎖住, 彼此都在等對方釋放鎖, 故又稱為死鎖

以下的 MySQL 形容, 屬於 ACID 中的哪一種?
  • Example:
    一旦事務提交,則其所做的修改就會永久保存到數據庫中。此時即使系統崩潰,修改的數據也不會丟失。持久性是個有點模糊的概念,因為實際上持久性也分很多不同的級別。有些持久性策略能夠提供非常強的安全保障,而有些則未必。而且不可能有能做到100%的持久性保證的策略(如果數據庫本身就能做到真正的持久性,那麼備份又怎麼能增加持久性呢?)
  • Answer:
    durability (持久性)
以下的 MySQL 形容, 屬於 ACID 中的哪一種?
  • Example:
    通常來說,一個事務所做的修改在最終提交以前,對其他事務是不可見的
  • Answer:
    isolation (隔離性)
以下的 MySQL 形容, 屬於 ACID 中的哪一種?
  • Example:
    數據庫總是從一個一致性的狀態轉換到另外一個一致性的狀態。在前面的例子中,一致性確保了,即使在執行第三、四條語句之間時系統崩潰,支票賬戶中也不會損失200美元,因為事務最終沒有提交,所以事務中所做的修改也不會保存到數據庫中。
  • Answer:
    consistency (一致性)
以下的 MySQL 形容, 屬於 ACID 中的哪一種?
  • Example:
    一個事務必須被視為一個不可分割的最小工作單元,整個事務中的所有操作要麼全部提交成功,要麼全部失敗回滾,對於一個事務來說,不可能只執行其中的一部分操作
  • Answer:
    atomicity (原子性)
MySQL 中的 ACID, 分別是哪四種特性?

atomicity (原子性)
consistency (一致性)
isolation (隔離性)
durability (持久性)

以下的 MySQL Image 是??
  • Example:
  • Answer:
    MySQL 邏輯架構圖
以下的 MySQL example code 的意思是?
  • Example:

    mysqlbinlog --database=rookery  --start-position="1284889" --to-last-log \
    binaryLogAbsoluteLocation |
    mysql --user=userName --password
  • Answer:
    從 binary log 備份中, 從 position 1284889 開始復原, 直到最後一個 log, 復原範圍有包含 1284889

以下的 MySQL example code 的意思是?
  • Example:

    mysqlbinlog --database=databaseName--stop-position="1258707" \
    binaryLogAbsoluteLocation |
    mysql --user=userName --password
  • Answer:
    從 binary log 備份中, 復原到 position 1258707, 復原範圍不包含 1258707

以下的 MySQL example code 的意思是?
  • Example:

    # at 1258707
    #140916 13:10:24 server id 1 end_log_pos 1258778
    Query thread_id=382 exec_time=0 error_code=0
    SET TIMESTAMP=1410887424/*!*/;
    SET @@session.sql_mode=0/*!*/;

    BEGIN
    /*!*/;

    # at 1258778
    #140916 13:10:24 server id 1 end_log_pos 1258900
    Query thread_id=382 exec_time=0 error_code=0
    use `rookery`/*!*/;
    SET TIMESTAMP=1410887424/*!*/;

    DELETE FROM birds_simple WHERE common_name LIKE '%Blue%'
    /*!*/;

    # at 1258900
    #140916 13:10:24 server id 1 end_log_pos 1258927 Xid = 45248

    COMMIT/*!*/;
  • Answer:
    binary log 中的一次 transaction 的紀錄, 1258707 為起點, 1258900 為終點, 1258927 為下一個起點

以下的 MySQL example code 的意思是?
  • Example:

    mysqlbinlog --database=databaseName \
    /binaryLogAbsoluteLocation > whateverNameYouLike.txt
  • Answer:
    將 binary log 匯出成 txt 檔, 方便手動尋找 position

以下的 MySQL example code 的意思是?
  • Example:

    SHOW VARIABLES WHERE Variable_Name LIKE 'datadir';
  • Answer:
    取得實際檔案存放位置

以下的 MySQL example code 的意思是?
  • Example:

    SHOW MASTER STATUS;
  • Answer:
    顯示當前的 binary log

Binlog_Do_DB: 指定哪些資料庫才要記錄到 binary log
Binlog_Ignore_DB: 指定哪些資料庫不要記錄到 binary log
Executed_Gtid_Set: 已經執行的 transaction 編號

MySQL 中, 何謂 DDL?

Data Definition Language, 例如 CREATE, DROP, ALTER

以下的 MySQL example code 的意思是?
  • Example:

    SHOW BINARY LOGS
  • Answer:
    顯示 binary log 是否有啟動
    若有則顯示 log
    若無則返回錯誤 ERROR 1381 (HY000): You are not using binary logging

以下的 MySQL example code 的意思是?
  • Example:

    log-bin
    binlog-ignore-db=mysql
  • Answer:
    啟動 binary log 功能
    不 log mysql 這個 table, 安全考量

MySQL 中, 如果我想要從一個 dump file 中只復原一部分的 table, 該怎麼做?

修改 dump file, 只留下開頭, 結尾變數, 以及需要的 table 段落
或是修改 create database, 改成一個臨時的 database name, 匯出後在從該臨時 database 中取出資料, 完成後再刪掉該 temporary database
或是賦予一個臨時使用者該 table 的權限, 讓這個使用者去執行 restore

以下的 MySQL example code 的意思是?
  • Example:

    mysqldump --user=admin_backup --password --lock-tables \
    --databases rookery --tables birds > birds-humans.sql

    mysqldump --user=admin_backup --password --lock-tables \
    --databases birdwatchers --tables humans >> birds-humans.sql
  • Answer:
    備份兩個資料庫的不同資料表, 但只產生一個 dump file

以下的 shell script 的意思是?
  • Example:

    #!/bin/sh

    my_user='admin_back'
    my_pwd='my_silly_password'

    db1='rookery'
    db2='birdwatchers'

    date_today=$(date +%Y-%m-%d)

    backup_dir='/data/backup/'
    dump_file=$db1-$db2-$date_today'.sql'

    /usr/bin/mysqldump --user=$my_usr --password=$my_pwd --lock-tables \
    --databases $db1 $db2 > $backup_dir$dump_file

    exit
  • Answer:
    備份 MySQL Database 的 script

以下的 MySQL example code 的意思是?
  • Example:

    LOCK TABLES `bird_families` WRITE;

    /*!40000 ALTER TABLE `bird_families` DISABLE KEYS */;

    INSERT INTO `bird_families` VALUES

    ...

    /*!40000 ALTER TABLE `bird_families` ENABLE KEYS */;

    UNLOCK TABLES;
  • Answer:
    通常還原資料, 在執行 INSERT 敘述時, 會先 DISABLE KEYS, 等到 INSERT 都完成後, 再 ENABLE KEYS, 這樣的效率會比 INSERT 時一筆筆的 INSERT INDEX 來得好

MySQL dump 復原資料時, 預設會覆蓋掉原本存在的資料嗎?

會哦, 因為會先 drop 現存的同名 table, 再 insert

以下的 MySQL example code 的意思是?
  • Example:

    mysqldump --user=specifiedUserAndHostName \
    --password --lock-all-tables
    --all-databases > /preferredLocationAndFileName.sql
  • Answer:
    –user: 要求 mysqlsump 使用 admin_backup 這個帳號與 MySQL Server 互動
    –password: 指定使用帳號的密碼
    –lock-all-tables: 在開始備份前, 先把所有資料表鎖定
    –all-databases: 指定匯出所有資料庫

以下位於 dump 檔的 MySQL example code 的意思是?
  • Example:

    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  • Answer:
    *!40101: 當 MySQL 版本在 4.01.01 以上時, 該行指令才會執行
    設定一個變數 @OLD_CHARACTER_SET_CLIENT, 並將目前的通用變數 CHARACTER_SET_CLIENT 儲存起來

以下的 MySQL example code 的意思是?
  • Example:

    GRANT 'roleName' TO 'userName'@'hostName';
  • Answer:
    賦予使用者 Role 權限

以下的 MySQL example code 的意思是?
  • Example:

    CREATE ROLE 'roleName';
  • Answer:
    建立 Role, 可賦予 Role 權限, 在賦予使用者 Role

以下的 MySQL example code 的意思是?
  • Example:

    SET ROLE 'admin_import_role';

    LOAD DATA INFILE
    ...

    SET ROLE NONE;
  • Answer:
    當該使用者有被賦予該 Role 時, 可切換該 Role
    執行完權限後, 登出 Role

以下的 MySQL example code 的意思是?
  • Example:

    RENAME USER 'lena_stankoska'@'lena_stankoska_home'
    TO 'lena'@'stankoskahouse.com';
  • Answer:
    重新命名使用者帳號

以下的 MySQL example code 的意思是?
  • Example:

    SHOW PROCESSLIST;
  • Answer:
    當我們刪除一個使用者後, 如果該使用者在當下仍與 Server 處於一個連線狀態中, 那該使用者依然可以對資料庫進行操作, 這時可以透過 SHOW PROCESSLIST 列出連線, 並使用 kill processId 刪除該連線

MySQL 中, 當我刪除一個使用者後, 如果該使用者在當下仍跟 Server 處於連線狀態, 該使用者還能操作資料庫嗎?

可以

以下的 MySQL example code 的意思是?
  • Example:

    REVOKE ALL PRIVILEGES
    ON rookery.*
    FROM 'michael_stone'@'localhost'
  • Answer:
    使用 REVOKE 語法, 從 'michael_stone@localhost' user 身上收回 rookery 資料庫的所有的權限

以下的 MySQL example code 的意思是?
  • Example:

    GRANT ALL PRIVILEGES ON rookery.*
    TO 'admin_granter'@'localhost'
    IDENTIFIED BY 'avocet_123'
    WITH GRANT OPTION;

    GRANT ALL PRIVILEGES ON birdwatchers.*
    TO 'admin_granter'@'localhost'
    IDENTIFIED BY 'avocet_123'
    WITH GRANT OPTION;
  • Answer:
    透過 WITH GRANT OPTION 語法, 可以將自身擁有的權限賦予給其他帳號

以下的 MySQL example code 的意思是?
  • Example:

    CREATE USER 'admin_import'@'localhost'
    IDENTIFIED BY 'another_pwd_789';

    GRANT FILE ON *.*
    TO 'admin_import'@'localhost';
  • Answer:
    建立一個可從檔案匯入資料的帳號, 擁有權限的帳號相當於可以讀取 MySQL 在主機上有權觸及的任何檔案, 並且可以把資料匯入到任何一個資料庫, 反之, 也可從資料庫會出資料, 甚至是含有密碼的資料庫

以下的 MySQL example code 的意思是?
  • Example:

    CREATE USER 'admin_restore'@'localhost'
    IDENTIFIED BY 'different_pwd_456';

    GRANT INSERT, LOCK TABLES, CREATE,
    CREATE TEMPORARY TABLES, INDEX, ALTER
    ON rookery.*
    TO 'admin_restore'@'localhost';

    GRANT INSERT, LOCK TABLES, CREATE,
    CREATE TEMPORARY TABLES, INDEX, ALTER
    ON birdwatchers.*
    TO 'admin_restore'@'localhost';
  • Answer:
    建立一個專門還原備份資料的使用者
    要從 dump file 復原, 所以必須要具有 INSERT 權限
    復原時為了能鎖定資料表, 因此需要 LOCK TABLES 權限
    要重建資料表, 因此需要 CREATE 權限
    要重建 index, 因此需要 INDEX 權限
    dump 內的 SQL 敘述可能會修改資料表, 因此需要 ALTER 權限
    如果想把資料還原至臨時資料表, 那就需要 CREATE TEMPORARY TABLES 權限
    如果還有包含 TRIGGER 或 VIEW, 那就要加上 CREATE VIEW 和 TRIGGER 權限

以下的 MySQL example code 的意思是?
  • Example:

    CREATE USER 'admin_backup'@'localhost'
    IDENTIFIED BY 'its_password_123';

    GRANT SELECT, LOCK TABLES
    ON rookery.*
    TO 'admin_backup'@'localhost';

    GRANT SELECT, LOCK TABLES
    ON birdwatchers.*
    TO 'admin_backup'@'localhost';
  • Answer:
    建立一個管理備份用的帳號, 賦予 SELECT 權限, 所以可讀取資料庫, 因為在備份的過程中必須 lock table, 所以賦予 LOCK TABLES 權限, 只限定特定資料庫

以下的 Shell script example code 的意思是?
  • Example:

    #!/bin/sh

    mysql_connect="mysql --user root -pmy_pwd"

    results=`$mysql_connect --skip-column-names \
    --execute 'SHOW TABLES FROM birdwatchers;'`

    items=$(echo $results | tr " " "\n")

    for item in $items
    do

    if [ $item = 'humans' ] ||
    [ $item = 'birder_families' ] ||
    [ $item = 'birding_events_children' ]
    then
    continue
    fi

    `$mysql_connect --execute "GRANT SELECT ON birdwatchers.$item \
    TO 'lena_stankoska'@'lena_stankoska_home'"`
    done

    exit
  • Answer:
    由於 MySQL 中, 如果要 GRANT 特定權限給特定 table 必須要一個一個指定, 所以使用 shell script 來批量 GRANT

以下的 MySQL example code 的意思是?
  • Example:

    SELECT COLUMN_GET(choices, answer AS CHAR)
    AS 'Birding Site',
    COUNT(*) AS 'Votes',
    CONCAT( TRUNCATE( (COUNT(*) / @fav_site_total) * 100, 1), '%')
    AS 'Percent'
    FROM survey_answers
    JOIN survey_questions USING(question_id)
    WHERE survey_id = 1
    AND question_id = 1
    GROUP BY answer;
  • Answer:
    TRUNCATE() 的 arg1 表示要處理的 input, arg2 表示小數位 1 位之後都要拿掉

以下的 MySQL function 的差異是?
  • Example:

    ROUND()
    FLOOR()
    CEILING()
  • Answer:
    四捨五入, 無條件捨去, 無條件進位

以下的 MySQL example code 的意思是?
  • Example:

    SELECT COLUMN_GET(choices, answer AS CHAR)
    AS 'Birding Site',
    COUNT(*) AS 'Votes',
    (COUNT(*) / @fav_site_total) AS 'Percent'
    FROM survey_answers
    JOIN survey_questions USING(question_id)
    WHERE survey_id = 1
    AND question_id = 1
    GROUP BY answer;
  • Answer:
    @fav_site_total 為 variable, 需事先定義

以下的 MySQL example code 的意思是?
  • Example:

    SET @fav_site_total =
    (SELECT COUNT(*)
    FROM survey_answers
    JOIN survey_questions USING(question_id)
    WHERE survey_id = 1
    AND question_id = 1);

    SELECT @fav_site_total;
  • Answer:
    @fav_site_total 為變數, assign 一段 SQL query

以下的 MySQL example code 的意思是?
  • Example:

    SELECT IFNULL(COLUMN_GET(choices, answer AS CHAR), 'total')
    AS 'Birding Site', COUNT(*) AS 'Votes'
    FROM survey_answers
    JOIN survey_questions USING(question_id)
    WHERE survey_id = 1
    AND question_id = 1
    GROUP BY answer WITH ROLLUP;
  • Answer:
    使用 COLUMN_GET(), 從 choices column 取值。 其為 MariaDB 的 blob data type, 可以存多組 key/value, 並且取值時要指定型別

以下的 MySQL example code 的意思是?
  • Example:

    SELECT NOW(), event_date, start_time,
    CONCAT(
    DATEDIFF(event_date, DATE(NOW())), ' Days, ',
    DATE_FORMAT(TIMEDIFF(start_time, TIME(NOW())), '%k hours, %i minutes'))
    AS 'Time to Event'
    FROM birding_events;
  • Answer:
    使用 DATEDIFF() 取得 event_date 以及 NOW() 的差異天數
    使用 TIMEDIFF() 將 start_time 以及現在的時間的差異 小時, 分鐘 取出
    再使用 CONCAT 串起來

以下的 MySQL example code 的意思是?
  • Example:

    SELECT CURDATE() AS 'Today',
    DATE_FORMAT(membership_expiration, '%M %e, %Y')
    AS 'Date Membership Expires',
    DATEDIFF(membership_expiration, CURDATE())
    AS 'Days Until Expiration'
    FROM humans
    WHERE human_id = 4;
  • Answer:
    使用 DATEDIFF() 來比較兩個日期時間的差異天數

以下的 MySQL example code 的意思是?
  • Example:

    SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
    COUNT(time_seen) AS 'Sightings Recorded'
    FROM bird_sightings
    JOIN humans USING(human_id)
    WHERE CONCAT(QUARTER(time_seen), YEAR(time_seen)) =
    CONCAT(
    QUARTER(
    STR_TO_DATE(
    PERIOD_ADD( EXTRACT(YEAR_MONTH FROM CURDATE()), -3),
    '%Y%m') ),
    YEAR(
    STR_TO_DATE(
    PERIOD_ADD( EXTRACT(YEAR_MONTH FROM CURDATE()), -3),
    '%Y%m') ) )
    GROUP BY human_id LIMIT 5;
  • Answer:
    先用 EXTRACT() 將 YEAR_MONTH 從目前時間取出, 接著使用 PERIOD_ADD() 減掉 3 個月, 再用 STR_TO_DATE 設定成 ‘%Y%m’ 格式, 最後再用 QUARTER() 以及 YEAR 算出第幾季以及第幾年

以下的 MySQL example code 的意思是?
  • Example:

    SELECT TIME(NOW()),
    TIME_TO_SEC(NOW()),
    TIME_TO_SEC(NOW()) / 60 /60 AS 'Hours';
  • Answer:
    僅將 時間 部分轉換成秒數, 即今天至今已過了幾秒

以下的 MySQL example code 的意思是?
  • Example:

    UPDATE bird_sightings
    SET time_seen = DATE_ADD(time_seen, INTERVAL '1 2' DAY_HOUR)
    WHERE sighting_id = 16;
  • Answer:
    使用 DATE_ADD(), 增加 1 天 2 小時

以下的 MySQL example code 的意思是?
  • Example:

    UPDATE humans
    SET membership_expiration = DATE_ADD(membership_expiration, INTERVAL -1 YEAR)
    WHERE CONCAT(name_first, SPACE(1), name_last) = 'Melissa Lee';
  • Answer:
    當使用負號, DATE_ADD() 也有 DATE_SUB() 的效果

以下的 MySQL example code 的意思是?
  • Example:

    UPDATE humans
    SET membership_expiration = DATE_SUB(membership_expiration, INTERVAL 1 YEAR)
    WHERE CONCAT(name_first, SPACE(1), name_last) = 'Melissa Lee';
  • Answer:
    更新 membership_expiration, 使用 DATE_SUB 扣掉一年

以下的 MySQL example code 的意思是?
  • Example:

    UPDATE humans
    SET membership_expiration = DATE_ADD(membership_expiration, INTERVAL 3 MONTH)
    WHERE country_id = 'uk'
    AND membership_expiration > CURDATE( );
  • Answer:
    更新 membership_expiration, 使用 DATE_ADD 將日期往後增加三個月

以下的 MySQL my.cnf example code 的意思是?
  • Example:

    default-time-zone='GMT'
  • Answer:
    將 server 的預設時區改為 GMT

以下的 MySQL example code 的意思是?
  • Example:

    SELECT common_name AS 'Bird',
    CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
    DATE_FORMAT(time_seen, '%r') AS 'System Time Spotted',
    DATE_FORMAT(CONVERT_TZ(time_seen, 'US/Eastern', 'Europe/Rome'), '%r')
    AS 'Birder Time Spotted'
    FROM bird_sightings
    JOIN humans USING(human_id)
    JOIN rookery.birds USING(bird_id)
    JOIN rookery.conservation_status USING(conservation_status_id) LIMIT 3;
  • Answer:
    使用 CONVERT_TZ, arg1 為時間, arg2 為來源時區, arg3 為欲轉換成的時區, 再將結果作為 parameter 帶入 DATE_FORMAT()

以下的 MySQL example code 的意思是?
  • Example:

    SHOW VARIABLES LIKE 'time_zone';
  • Answer:
    取得 MySQL 系統時區

以下的 MySQL example code 的意思是?
  • Example:

    SELECT DATE_FORMAT(CURDATE(), GET_FORMAT(DATE,'EUR'))
    AS 'Date in Europe',
    DATE_FORMAT(CURDATE(), GET_FORMAT(DATE,'USA'))
    AS 'Date in U.S.',
    REPLACE(DATE_FORMAT(CURDATE(), GET_FORMAT(DATE,'USA')), '.', '-')
    AS 'Another Date in U.S.';
  • Answer:
    使用 GET_FORMAT 取得特定地區的時間格式, 再將結果帶入 DATE_FORMAT 完成格式設定
    使用 REPLACE 將不喜歡的格式做客製化替換

以下的 MySQL example code 的意思是?
  • Example:

    SELECT common_name AS 'Endangered Bird',
    CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
    DATE_FORMAT(time_seen, '%W, %M %e, %Y') AS 'Date Spotted',
    TIME_FORMAT(time_seen, '%l:%i %p') AS 'Time Spotted'
    FROM bird_sightings
    JOIN humans USING(human_id)
    JOIN rookery.birds USING(bird_id)
    JOIN rookery.conservation_status USING(conservation_status_id)
    WHERE conservation_category = 'Threatened' LIMIT 3;
  • Answer:
    使用 DATE_FORMAT, TIME_FORMAT 從指定時間擷取並輸入定義的格式, 格式碼可參考

以下的 MySQL example code 的意思是?
  • Example:

    SELECT time_seen,
    EXTRACT(YEAR_MONTH FROM time_seen) AS 'Year & Month',
    EXTRACT(MONTH FROM time_seen) AS 'Month Only',
    EXTRACT(HOUR_MINUTE FROM time_seen) AS 'Hour & Minute',
    EXTRACT(HOUR FROM time_seen) AS 'Hour Only'
    FROM bird_sightings JOIN humans USING(human_id)
    LIMIT 3;
  • Answer:
    使用 EXTRACT() 從指定時間中擷取特定格式, EXTRACT() 支援格式可參考

以下的 MySQL example code 的意思是?
  • Example:

    SELECT time_seen,
    EXTRACT(YEAR_MONTH FROM time_seen) AS 'Year & Month',
    EXTRACT(MONTH FROM time_seen) AS 'Month Only',
    EXTRACT(HOUR_MINUTE FROM time_seen) AS 'Hour & Minute',
    EXTRACT(HOUR FROM time_seen) AS 'Hour Only'
    FROM bird_sightings JOIN humans USING(human_id)
    LIMIT 3;
  • Answer:
    使用 EXTRACT() 從指定時間中擷取特定格式, EXTRACT() 支援格式可參考

以下的 MySQL example code 的意思是?
  • Example:

    SELECT common_name AS 'Endangered Bird',
    CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
    CONCAT(DAYNAME(time_seen), ', ', MONTHNAME(time_seen), SPACE(1),
    DAY(time_seen), ', ', YEAR(time_seen)) AS 'Date Spotted',
    CONCAT(HOUR(time_seen), ':', MINUTE(time_seen),
    IF(HOUR(time_seen) < 12, ' a.m.', ' p.m.')) AS 'Time Spotted'
    FROM bird_sightings
    JOIN humans USING(human_id)
    JOIN rookery.birds USING(bird_id)
    JOIN rookery.conservation_status USING(conservation_status_id)
    WHERE conservation_category = 'Threatened' LIMIT 3;
  • Answer:

    使用 DAYNAME(), MONTHNAME, 分別擷取帶入時間, 並輸出成 Day 的英文單字, 月份英文單字
    使用 DAY(), YEAR(), HOUR(), MINUTE(), 分別擷取帶入時間的 , , ,
    最後判斷, 如果 HOUR 小於 12, 即為上午 a.m., 大於 12, 即下午 p.m.

以下的 MySQL example code 的意思是?
  • Example:

    SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
    time_seen, YEAR(time_seen), MONTH(time_seen), DAY(time_seen),
    MONTHNAME(time_seen), DAYNAME(time_seen)
    FROM bird_sightings JOIN humans USING(human_id)
    WHERE bird_id = 309 \G
  • Answer:

    使用 YEAR(), MONTH(), DAY(), 分別擷取帶入時間的 , ,
    使用 MONTHNAME(), DAYNAME(), 分別擷取並輸出成帶入時間的 月份英文單字, day 的英文單字

以下的 MySQL example code 的意思是?
  • Example:

    SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
    time_seen, HOUR(time_seen), MINUTE(time_seen), SECOND(time_seen)
    FROM bird_sightings JOIN humans USING(human_id)
    WHERE bird_id = 309 \G
  • Answer:

    使用 HOUR(), MINUTE(), SECOND(), 分別擷取帶入時間的 小時, ,

以下的 MySQL example code 的意思是?
  • Example:

    SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
    time_seen, DATE(time_seen), TIME(time_seen)
    FROM bird_sightings
    JOIN humans USING(human_id)
    WHERE bird_id = 309;
  • Answer:

    使用 DATE(), TIME() 分別擷取帶入時間的 年月日 以及 時間 部分

以下的 MySQL example code 的意思是?
  • Example:

    SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
    ROUND((UNIX_TIMESTAMP( ) - UNIX_TIMESTAMP(time_seen)) / 60 / 60 / 24)
    AS 'Days Since Spotted'
    FROM bird_sightings JOIN humans USING(human_id)
    WHERE bird_id = 309;
  • Answer:

    SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
    # UNIX_TIMESTAMP() 為取得 1970 年 1 月 1 號至今所經過的秒數
    # 帶入特定時間點並經過計算後, 可取得特定時間點至今所經過的天數
    ROUND((UNIX_TIMESTAMP() - UNIX_TIMESTAMP(time_seen)) / 60 / 60 / 24)
    AS 'Days Since Spotted'
    FROM bird_sightings JOIN humans USING(human_id)
    WHERE bird_id = 309;
以下的 MySQL example code 的意思是?
  • Example:

    SELECT NOW(), CURDATE(), CURTIME();
  • Answer:

    分別是取得當前日期時間, 當前日期, 當前時間

以下的 MySQL example code 的意思是?
  • Example:

    SELECT NOW(), SLEEP(4) AS 'Zzz', SYSDATE(), SLEEP(2) AS 'Zzz', SYSDATE();
  • Answer:

    NOW() 是取得 SQL Query 執行當下的時間, 而 SYSDATE() 是取得該函式執行當下的時間, 如果要在複雜的 SQL Query 當中取得某段函式的執行時間時, 便可使用 SYSDATE()

MySQL 中, 以下幾個函式的功能都一樣, 為什麼需要這麼多不同名稱卻同功能的函示?
  • Example:
    NOW(), CURRENT_TIMESTAMP(), LOCALTIME(), LOCALTIMESTAMP()
  • Answer:
    因為為了配合其他種類的資料庫, 像是 PostgreSQL, Oracle, Sybase 等等…
    這樣原本使用其他資料庫的應用程式就可以在不更動程式碼下直接更換資料庫
以下的 MySQL example code 的意思是?
  • Example:

    SELECT UNCOMPRESS(birding_background) AS Background
    FROM humans
    WHERE name_first = 'Melissa' AND name_last = 'Lee' \G
  • Answer:
    UNCOMPRESS function 可以解壓縮 COMPRESS function 壓縮過的欄位資料

以下的 MySQL example code 的意思是?
  • Example:

    INSERT INTO humans
    (formal_title, name_first, name_last, join_date, birding_background)
    VALUES('Ms', 'Melissa', 'Lee', CURDATE(), COMPRESS("lengthy background..."));
  • Answer:
    COMPRESS function 可以壓縮字串, 適用於大量的 text, 效能有待研究

以下的 MySQL example code 的意思是?
  • Example:

    SELECT bird_name, gender_age, bird_image
    FROM bird_images
    WHERE bird_name LIKE '%Plover%'
    ORDER BY CONVERT(gender_age USING utf8)
    LIMIT 5;
  • Answer:
    將 gender_age 的 character set 暫時轉為 utf8

以下的 MySQL example code 的意思是?
  • Example:

    SELECT bird_name, gender_age, bird_image
    FROM bird_images
    WHERE bird_name LIKE '%Plover%'
    ORDER BY CONVERT(gender_age, CHAR)
    LIMIT 5;
  • Answer:
    將 gender_age 的 column type 暫時轉換為 CHAR, 只反映在結果上, 並不會真正更動資料庫

MySQL 中, 如果 column type 是 enum, 那排序上會依照 enum 的排序還是 collation 的排序?

enum 的排序

以下的 MySQL example code 的意思是?
  • Example:

    SELECT sorting_id, bird_name, bird_image
    FROM bird_images ORDER BY CAST(sorting_id AS INT) LIMIT 5;
  • Answer:
    將 sorting_id column type 暫時轉換為 INT, 只作用到結果, 不會變動 column type

以下的 MySQL example 為什麼 sorting_id 的排列方式是如此?
  • Example:

    SELECT sorting_id, bird_name, bird_image
    FROM bird_images
    ORDER BY sorting_id
    LIMIT 5;
  • Answer:
    因為 sorting_id column 的 column type 被設為 char, 而非 int

以下的 MySQL example code 的意思是?

  • Example:

    SELECT common_name AS Original,
    REPLACE(common_name, 'Gt.', 'Great') AS Replaced
    FROM birds
    WHERE common_name REGEXP 'Gt.' LIMIT 1;
  • Answer:
    REPLACE() 跟 REPLACE 不同
    直接尋找 ‘Gt.’ 並將之替換為 ‘Great’, 只改變顯示結果, 並不會真正的更動資料庫

以下的 MySQL example code 的意思是?

  • Example:

    SELECT common_name AS Original,
    INSERT(common_name, LOCATE('Gt.', common_name), 3, 'Great') AS Adjusted
    FROM birds
    WHERE common_name REGEXP 'Gt.' LIMIT 1;
  • Answer:
    INSERT() 跟 INSERT 不同, INSERT() 只會將結果替換掉指定的字串, 不會真正的更動資料庫
    arg1 為要作用的欄位, arg2 為替換起始位置, arg3 為總共要覆蓋原本字串多少個字, arg4 為要用來替換的字串
    如上 example, LOCATE 會回傳 1, 所以從 1 開始替換 ‘Gt.’ 共三個字, 所以會全部覆蓋掉
    若不希望覆蓋掉任何字, arg3 可指定 0

以下的 MySQL example code 的意思是?
  • Example:

    SELECT CONCAT(name_first, SPACE(1), name_last) AS Name,
    common_name AS Bird,
    SUBSTRING(comments, 1, 25) AS Comments
    FROM birdwatchers.bird_sightings
    JOIN birdwatchers.humans USING(human_id)
    JOIN rookery.birds USING(bird_id)
    WHERE MATCH (comments) AGAINST ('beautiful');
  • Answer:

    SELECT CONCAT(name_first, SPACE(1), name_last) AS Name,
    common_name AS Bird,
    # 從第 1 個位置開始取, 取 25 個字
    SUBSTRING(comments, 1, 25) AS Comments
    FROM birdwatchers.bird_sightings
    JOIN birdwatchers.humans USING(human_id)
    JOIN rookery.birds USING(bird_id)
    # 唯有先行建立 FULLTEXT INDEX, 方可使用 MATCH ... AGAINST 函數
    # 從 'comments' column 尋找 'beautiful' 字串
    WHERE MATCH (comments) AGAINST ('beautiful');
以下的 MySQL example code 的意思是?
  • Example:

    CREATE FULLTEXT INDEX comment_index
    ON bird_sightings (comments);
  • Answer:
    建立 FULLTEXT index 名為 comment_index, 唯有建立 FULLTEXT index 方可使用 match … against 函數

以下的 MySQL example code 的意思是?
  • Example:

    INSERT IGNORE INTO possible_duplicate_email
    (human_id, email_address_1, email_address_2, entry_date)
    VALUES(LAST_INSERT_ID(), 'bobyfischer@mymail.com', 'bobbyfischer@mymail.com')
    WHERE ABS( STRCMP('bobbyrobin@mymail.com', 'bobyrobin@mymail.com') ) = 1 ;
  • Answer:

    INSERT IGNORE INTO possible_duplicate_email
    (human_id, email_address_1, email_address_2, entry_date)
    VALUES(LAST_INSERT_ID(), 'bobyfischer@mymail.com', 'bobbyfischer@mymail.com')
    ## ABS 會將 value 轉為絕對值, 即 -1 變成 1, 1 還是 1
    ## STRCMP 會比較兩個數, 相等則為 0, 否則則為 1 或 -1
    WHERE ABS( STRCMP('bobbyrobin@mymail.com', 'bobyrobin@mymail.com') ) = 1 ;
以下的 MySQL example code 的意思是?
  • Example:

    SELECT IF(CHAR_LENGTH(comments) > 100), 'long', 'short')
    FROM bird_sightings
    WHERE sighting_id = 2;
  • Answer:
    select 'comments' 欄位, 如果長度大於 100, 則回傳 'long', 反之則回傳 'short'

以下的 MySQL example code 的意思是?
  • Example:

    SELECT FIND_IN_SET('Anahit Vanetsyan', Names) AS Position
    FROM
    (SELECT GROUP_CONCAT(Name ORDER BY join_date) AS Names
    FROM
    ( SELECT CONCAT(name_first, SPACE(1), name_last) AS Name,
    join_date
    FROM humans
    WHERE country_id = 'ru')
    AS derived_1 )
    AS derived_2;
  • Answer:
    GROUP_CONCAT 會將 Name column 的所有姓名連接起來, 並 ORDER BY join_date, 然後 FIND_IN_SET() 從 Name column 中找到 ‘Anahit Vanetsyan’ 的位置

以下的 MySQL example code 的意思是?
  • Example:

    SELECT
    SUBSTRING(common_name, 1, LOCATE(' Avocet', common_name) ) AS 'Avocet'
    FROM birds
    JOIN bird_families USING(family_id)
    WHERE bird_families.scientific_name = 'Recurvirostridae'
    AND birds.common_name LIKE '%Avocet%';
  • Answer:
    LOCATE 會取得 ‘ Avocet’ 前面那一個空格的位置, 而 SUBSTRING 從第 1 個位置開始取, 取 LOCATE 得到的結果, 正好把 ‘Avocet’ 濾掉

以下的 MySQL query 的意思是?
  • Example:

    # +--------------------------------------------------------+
    # | prospect_name |
    # +--------------------------------------------------------+
    # | Ms.| Caryn-Amy | Rose |
    # | Mr.| Colin | Charles |
    # | Mr.| Kenneth | Dyer |
    # | Ms.| Sveta | Smirnova |
    # +--------------------------------------------------------+

    SELECT SUBSTRING_INDEX(prospect_name, '|', 1) AS title,
    SUBSTRING_INDEX( SUBSTRING_INDEX(prospect_name, '|', 2), '|', -1) AS first_name,
    SUBSTRING_INDEX(prospect_name, '|', -1) AS last_name
    FROM prospects WHERE prospect_id = 7;
  • Answer:

    // 取到第一個 '|' 之前的所有字串
    SELECT SUBSTRING_INDEX(prospect_name, '|', 1) AS title,
    // 內函數取得第兩個 '|' 之前的所有字串, 外函數則是從尾端往前找, 直到找到 '|', 取這之間的字串
    SUBSTRING_INDEX( SUBSTRING_INDEX(prospect_name, '|', 2), '|', -1) AS first_name,
    // 從尾端往前找, 直到找到 '|', 取這之間的字串
    SUBSTRING_INDEX(prospect_name, '|', -1) AS last_name
    FROM prospects WHERE prospect_id = 7;
以下的 MySQL query 的意思是?
  • Example:

    # +--------------------------------------------------------+
    # | prospect_name |
    # +--------------------------------------------------------+
    # | Ms. Caryn-Amy Rose |
    # | Mr. Colin Charles |
    # | Mr. Kenneth Dyer |
    # | Ms. Sveta Smirnova |
    # +--------------------------------------------------------+

    SELECT SUBSTRING(prospect_name, 1, 2) AS title,
    SUBSTRING(prospect_name FROM 5 FOR 25) AS first_name,
    SUBSTRING(prospect_name, -25) AS last_name
    FROM prospects LIMIT 3;
  • Answer:

    // 從第 1 個開始取, 取 2 個
    SELECT SUBSTRING(prospect_name, 1, 2) AS title,
    // 從第 5 個開始取, 取 25 個
    SUBSTRING(prospect_name FROM 5 FOR 25) AS first_name,
    // 取後面算來 25 個
    SUBSTRING(prospect_name, -25) AS last_name
    FROM prospects LIMIT 3;
以下的 MySQL query 的意思是?
  • Example:

    # +--------------------------------------------------------+
    # | prospect_name |
    # +--------------------------------------------------------+
    # | Ms. Caryn-Amy Rose |
    # | Mr. Colin Charles |
    # | Mr. Kenneth Dyer |
    # | Ms. Sveta Smirnova |
    # +--------------------------------------------------------+

    SELECT LEFT(prospect_name, 2) AS title,
    MID(prospect_name, 5, 25) AS first_name,
    RIGHT(prospect_name, 25) AS last_name
    FROM prospects LIMIT 4;
  • Answer:
    LEFT: 從左邊開始取, 取 2 個
    MID: 從第 5 個開始取, 取 25 個
    RIGHT: 從後面往前取, 取 25 個

以下的 MySQL query 的意思是?
  • Example:

    SELECT CONCAT(RPAD(common_name, 20, '.' ),
    RPAD(Families.scientific_name, 15, '.'),
    Orders.scientific_name) AS Birds
    FROM birds
    JOIN bird_families AS Families USING(family_id)
    JOIN bird_orders AS Orders
    WHERE common_name != ''
    AND Orders.scientific_name = 'Ciconiiformes'
    ORDER BY common_name LIMIT 3;

    # +--------------------------------------------------+
    # | Birds |
    # +--------------------------------------------------+
    # | Abbott's Babbler....Pellorneidae...Ciconiiformes |
    # | Abbott's Booby......Sulidae........Ciconiiformes |
    # | Abbott's Starling...Sturnidae......Ciconiiformes |
    # +--------------------------------------------------+
  • Answer:
    利用 RPAD 將空格填滿, arg1 為作用的 column, arg2 為該 column 預期的字串長度, arg3 為不足字串長度時, 要使用什麼字符將之補滿

以下的 MySQL query 的意思是?
  • Example:

    SELECT QUOTE(common_name)
    FROM birds
    WHERE common_name LIKE "%Prince%"
    ORDER BY common_name;
  • Answer:

    # 將資料夾上 quote 輸出, 並 escape 特殊符號
    SELECT QUOTE(common_name)
    FROM birds
    WHERE common_name LIKE "%Prince%"
    ORDER BY common_name;

    # +----------------------------------+
    # | QUOTE(common_name) |
    # +----------------------------------+
    # | 'Prince Henry\'s Laughingthrush' |
    # | 'Prince Ruspoli\'s Turaco' |
    # | 'Princess Parrot' |
    # +----------------------------------+
以下的 MySQL query 的意思是?
  • Example:

    SELECT LCASE(common_name) AS Species,
    UCASE(bird_families.scientific_name) AS Family
    FROM birds
    JOIN bird_families USING(family_id)
    WHERE common_name LIKE '%Wren%'
    ORDER BY Species
    LIMIT 5;
  • Answer:

    ## 將 common_name column 的值轉為小寫
    SELECT LCASE(common_name) AS Species,
    ## 將 scientific_name column 的值轉為大寫
    UCASE(bird_families.scientific_name) AS Family
    FROM birds
    ## 經由 family_id JOIN 兩張表
    JOIN bird_families USING(family_id)
    WHERE common_name LIKE '%Wren%'
    ORDER BY Species
    LIMIT 5;

    # +-------------------------+---------------+
    # | Species | Family |
    # +-------------------------+---------------+
    # | apolinar's wren | TROGLODYTIDAE |
    # | band-backed wren | TROGLODYTIDAE |
    # | banded wren | TROGLODYTIDAE |
    # | bar-winged wood-wren | TROGLODYTIDAE |
    # | bar-winged wren-babbler | TIMALIIDAE |
    # +-------------------------+---------------+
以下的 MySQL query 的意思是?
  • Example:

    mysql -p --skip-column-names -e \
    "SELECT CONCAT_WS('|', IFNULL(formal_title, ' '), IFNULL(name_first, ' '),
    IFNULL(name_last, ' '), IFNULL(street_address, ' '),
    IFNULL(city, ' '), IFNULL(state_province, ' '),
    IFNULL(postal_code, ' '), IFNULL(country_id, ' '))
    FROM birdwatchers.humans WHERE membership_type = 'premium'
    AND membership_expiration > CURDATE();" > rookery_patch_mailinglist.txt
  • Answer:

    mysql -p --skip-column-names -e \
    # 跳過欄位那一欄不做處理, -p 代表 password, -e 代表執行 "" 內的程式碼
    # 使用 '|' 來連接並隔開每個 column 的value
    # CONCAT_WS, WS 為 with seprarator 的意思
    "SELECT CONCAT_WS('|', IFNULL(formal_title, ' '), IFNULL(name_first, ' '),
    # 加上 IFNULL, 若 NULL 的話也會輸出空字串, 否則 CONCAT_WS 會不處理直接跳過
    # 造成 select 8 個欄位, 但卻只輸出 4 個欄位的值, 也不知道哪個欄位沒值
    IFNULL(name_last, ' '), IFNULL(street_address, ' '),
    IFNULL(city, ' '), IFNULL(state_province, ' '),
    IFNULL(postal_code, ' '), IFNULL(country_id, ' '))
    FROM birdwatchers.humans WHERE membership_type = 'premium'
    # 將結果輸出到 txt 檔
    AND membership_expiration > CURDATE();" > rookery_patch_mailinglist.txt
以下的 MySQL query 的意思是?
  • Example:

    SELECT common_name AS 'Bird',
    conservation_state AS 'Status'
    FROM birds
    LEFT JOIN conservation_status USING(conservation_status_id)
    WHERE common_name LIKE '%Egret%'
    ORDER BY Status, Bird;
  • Answer:
    從 birds table select common_name column (命名呈現的 column name 為 Bird), conservation_state column (命名呈現的 column name 為 Status)
    left join conservation_status table, 透過 conservation_status_id
    left join 會先取出 birds table, 所以如果並沒有對應的 conservation_status_id 的話, 就會顯示 null
    像是

    +--------------------+-----------------+
    | Bird | Status |
    +--------------------+-----------------+
    | Great Egret | NULL |
    | Cattle Egret | Least Concern |
    | Intermediate Egret | Least Concern |
    | Little Egret | Least Concern |
    | Snowy Egret | Least Concern |
    | Reddish Egret | Near Threatened |
    | Chinese Egret | Vulnerable |
    | Slaty Egret | Vulnerable |
    +--------------------+-----------------+
以下的 MySQL query 的意思是?
  • Example:

    SELECT book_id, title, status_name
    FROM books
    JOIN status_names USING(status_id);
  • Answer:
    JOIN books 跟 status_names 兩張 table, 因為要用來 connect 兩張 table 的 key 都一樣, 因此使用 USING

以下的 MySQL query 的意思是?
  • Example:

    SELECT book_id, title, status_name
    FROM books
    JOIN status_names ON(status = status_id);
  • Answer:
    JOIN books 跟 status_names 兩張 table, 用 ON 連結 status 以及 status_id 欄位

以下的 MySQL query 的意思是?
  • Example:

    SELECT 'Pelecanidae' AS 'Family',
    COUNT(*) AS 'Species'
    FROM birds, bird_families AS families
    WHERE birds.family_id = families.family_id
    AND families.scientific_name = 'Pelecanidae'
    UNION
    SELECT 'Ardeidae',
    COUNT(*)
    FROM birds, bird_families AS families
    WHERE birds.family_id = families.family_id
    AND families.scientific_name = 'Ardeidae';
  • Answer:

        ## 'Pelacanidae' 為不存在的欄位, 以純文字賦予欄位值, 結果如下
    SELECT 'Pelecanidae' AS 'Family',
    ## Species 為另一個 column
    COUNT(*) AS 'Species'
    FROM birds, bird_families AS families
    WHERE birds.family_id = families.family_id
    AND families.scientific_name = 'Pelecanidae'
    UNION
    ## 因為使用 Union, 這邊不須再加 alias, 會自動歸類到 'Family' column
    SELECT 'Ardeidae',
    COUNT(*)
    FROM birds, bird_families AS families
    WHERE birds.family_id = families.family_id
    AND families.scientific_name = 'Ardeidae';

    ## +-------------+---------+
    ## | Family | Species |
    ## +-------------+---------+
    ## | Pelecanidae | 10 |
    ## | Ardeidae | 157 |
    ## +-------------+---------+
以下的 MySQL query 的意思是?
  • Example:

    DELETE FROM humans, prize_winners
    USING humans JOIN prize_winners
    WHERE name_first = 'Elena'
    AND name_last = 'Bokova'
    AND email_address LIKE '%yahoo.com'
    AND humans.human_id = prize_winners.human_id;
  • Answer:

    // 同時 delete humans 以及 prize_winners 的資料
    // 如果不加 prize_winners, 那該 table 上的資料就
    // 不會被刪除
    DELETE FROM humans, prize_winners
    // JOIN prize_winners table
    USING humans JOIN prize_winners
    WHERE name_first = 'Elena'
    AND name_last = 'Bokova'
    AND email_address LIKE '%yahoo.com'
    AND humans.human_id = prize_winners.human_id;
以下的 MySQL query 的意思是?
  • Example:

    CREATE TEMPORARY TABLE possible_duplicates
    (name_1 varchar(25), name_2 varchar(25));
  • Answer:
    建立一個臨時的 table, 當退出 MySQL client 時, 該 table 會自動銷毀

以下的 MySQL query 的意思是?
  • Example:

    INSERT INTO humans
    (formal_title, name_first, name_last, email_address, better_birders_site)
    VALUES('Mr','Barry','Pilson', 'barry@gomail.com', 1),
    ('Ms','Lexi','Hollar', 'alexandra@mysqlresources.com', 1),
    ('Mr','Ricky','Adams', 'ricky@gomail.com', 1)
    ON DUPLICATE KEY
    UPDATE better_birders_site = 2;
  • Answer:
    因為 email_address 有 unique 特性, 所以當 insert 過程中發現有 duplicate 的 row 時, 會 update 該 row 的 better_birders_site column 為 2

以下的 MySQL query 錯誤的原因是?
  • Example:

    UPDATE prize_winners, humans
    SET winner_date = CURDATE()
    WHERE winner_date IS NULL
    AND country_id = 'uk'
    AND prize_winners.human_id = humans.human_id
    ORDER BY RAND()
    LIMIT 2;
  • Answer:
    order by 及 limit 只適用於單一 table, 當 update 複數的 table 時就會出錯

以下的 MySQL query 的意思是?
  • Example:

    UPDATE prize_winners, humans
    SET winner_date = NULL,
    prize_chosen = NULL,
    prize_sent = NULL
    WHERE country_id = 'uk'
    AND prize_winners.human_id = humans.human_id;
  • Answer:
    從 human table 中取得 ‘country_id = uk’ 的 row, 再經此找到符合 ‘prize_winners.human_id = humans.human_id’ 的 prize_winners
    最後 update 這些 prize_winners 的 winner_date, prize_chosen, prize_sent columns

以下的 MySQL query 的意思是?
  • Example:

    ALTER TABLE humans
    CHANGE COLUMN formal_title formal_title ENUM('Mr.','Ms.','Mr','Ms');

    UPDATE humans
    SET formal_title = SUBSTRING(formal_title, 1, 2);

    ALTER TABLE humans
    CHANGE COLUMN formal_title formal_title ENUM('Mr','Ms');
  • Answer:

    ## 修改 humans table
    ALTER TABLE humans
    ## 將 column formal_title 改成 formal_title, type 為 ENUM('Mr.', 'Ms.', 'Mr', 'Ms');
    CHANGE COLUMN formal_title formal_title ENUM('Mr.','Ms.','Mr','Ms');

    ## 更新 humans table
    UPDATE humans
    ## 更新 formal_title column, 取 formal_title 的值, 從第一個字開始取, 共取兩個字
    ## 因此 "Mr." 會變成 "Mr"
    SET formal_title = SUBSTRING(formal_title, 1, 2);

    ## 同上, 修改 formal_title colum 的 type
    ALTER TABLE humans
    CHANGE COLUMN formal_title formal_title ENUM('Mr','Ms');
以下的 MySQL query 的意思是?
  • Example:

    select common_name as 'hawks'
    from birds
    where common_name regexp '[[:space:]]hawk|[[.hyphen.]]hawk'
    and common_name not regexp 'hawk-owl|hawk owl'
    order by family_id;
  • Answer:

    ## select common_name column, 並定義新欄位名稱為 Hawks
    select common_name as 'hawks'
    ## 從 birds table
    from birds
    ## common_name 的值必須是 " hawk", 或是 "-hawk"
    where common_name regexp '[[:space:]]hawk|[[.hyphen.]]hawk'
    ## common_name 的值必須不是 "hawk_owl" 或 "hawk owl"
    and common_name not regexp 'hawk-owl|hawk owl'
    ## 經由 family_id column 排序
    order by family_id;
以下的 MySQL column information 中, latin1_bin 的 bin 是什麼意思?
  • Example:

    SHOW FULL COLUMNS
    FROM birds LIKE 'common_name' \G

    *************************** 1. row ***************************
    Field: common_name
    Type: varchar(255)
    Collation: latin1_bin
    Null: YES
    Key:
    Default: NULL
    Extra:
    Privileges: select,insert,update,references
    Comment:
  • Answer:
    binary 的意思, 表示儲存在該欄位的資料都會以 binary 定序

以下的 MySQL example code 是什麼意思?
  • Example:

    SELECT common_name AS 'Hawks'
    FROM birds
    WHERE common_name REGEXP BINARY 'Hawk'
    AND common_name NOT REGEXP 'Hawk-Owl'
    ORDER BY family_id LIMIT 10;
  • Answer:
    一般來說, REGEXP 不會區分大小寫, 若要區分, 可加上 BINARY, 會以二進制定序, h 跟 H 的二進制碼不同, 所以可以區分大小寫

以下的 MySQL example code 是什麼意思?
  • Example:

    SELECT common_name, scientific_name, family_id
    FROM birds
    WHERE family_id IN(103, 160, 162, 164)
    AND common_name != ''
    ORDER BY common_name
    LIMIT 3, 2;
  • Answer:
    從第三筆之後開始查詢, 取得兩筆資料, 相當於 offset 3, limit 2

以下的 MySQL example code 是什麼意思?
  • Example:

    INSERT HIGH_PRIORITY INTO bird_sightings
  • Answer:
    MySQL 預設 insert 的 priority 就大於 select, 但如果預設值被改掉, 就可以使用上面的語法

以下的 MySQL example code 有個與 MySQL Client 不方便的地方, 是什麼?
  • Example:

    INSERT LOW_PRIORITY INTO bird_sightings
  • Answer:
    在 low priority instruction 被執行之前, client 會一直卡住

以下的 MySQL example code 是什麼意思?
  • Example:

    INSERT LOW_PRIORITY INTO bird_sightings
  • Answer:
    先完成手邊的工作, 再處理 low priority 的工作

以下的 MySQL example code 是什麼意思?
  • Example:

    REPLACE INTO bird_families
    (scientific_name, brief_description, order_id)
    VALUES('Viduidae', 'Indigobirds & Whydahs', 128),
    ('Estrildidae', 'Waxbills, Weaver Finches, & Allies', 128),
    ('Ploceidae', 'Weavers, Malimbe, & Bishops', 128);
  • Answer:
    將指定的 value insert 到指定的欄位, 如果遇到重複的, 則覆寫, 相當於 Laravel 中的 insertOrUpdate

以下的 MySQL example code 是什麼意思?
  • Example:

    UPDATE bird_families, bird_orders
    SET bird_families.order_id = bird_orders.order_id
    WHERE bird_families.order_id IS NULL
    AND cornell_bird_order = bird_orders.scientific_name;
  • Answer:
    將 bird_families table 中的 order_id update 為 bird_orders table 中的 order_id
    只更新 where clause 中限制的 record

以下的 MySQL example code 是什麼意思?
  • Example:

    INSERT IGNORE INTO bird_families
    (scientific_name, brief_description, cornell_bird_order)
    SELECT bird_family, examples, bird_order
    FROM cornell_birds_families_orders;
  • Answer:
    從 cornell_birds_families_orders table, select bird_family, examples, bird_order 三個欄位, 並將 select 的結果 insert 到 bird_families table 的 scientific_name, brief_description, cornell_bird_order 欄位, 同時, 若過程中出現錯誤, ignore 錯誤繼續執行

以下的 MySQL example code 中, 為什麼會失敗?
  • Example:

    ALTER TABLE conservation_status
    CHANGE status_id conservation_status_id INT AUTO_INCREMENT PRIMARY KEY;

    ERROR 1068: Multiple primary key defined
  • Answer:
    status_id 欄位原本已經有 primary_key, 這是一個 index
    上面的語法在原本已經有一個 index 的同時, 又要增加一個 index, 所以報錯
    應該要先把原本的 index 拿掉

以下的 MySQL example code 中, 為什麼最後一行的 common_name 要重複兩次?
  • Example:

    ALTER TABLE birds_new
    ADD COLUMN body_id CHAR(2) AFTER wing_id,
    ADD COLUMN bill_id CHAR(2) AFTER body_id,
    ADD COLUMN endangered BIT DEFAULT b'1' AFTER bill_id,
    CHANGE COLUMN common_name common_name VARCHAR(255);
  • Answer:
    第一次是指要變更的 column 名稱
    第二次是指要變更成哪個名稱

以下的 MySQL example code 中, 在建立 bill_id column 時, 理論上 body_id column 應該還沒創建出來, 那為什麼可以指定 bill_id after body_id 呢?
  • Example:

    ALTER TABLE birds_new
    ADD COLUMN body_id CHAR(2) AFTER wing_id,
    ADD COLUMN bill_id CHAR(2) AFTER body_id,
    ADD COLUMN endangered BIT DEFAULT b'1' AFTER bill_id,
    CHANGE COLUMN common_name common_name VARCHAR(255);
  • Answer:
    在 MySQL ALTER 的過程中, 會先建立一張虛擬的表格, 如果語法執行上都沒有問題, 會用這張虛擬表格取代原本的
    如果語法有錯, 則會刪除這張虛擬表格

MySQL 的 column property character set 以及 collation, 分別代表的意思是?

character set: 該欄位會儲存的語言屬於哪一種編碼
collation: 該欄位該以什麼樣的語種排序

以下的 MySQL command 的意思是?
  • Example:

    SHOW CREATE TABLE birds \G
  • Answer:
    顯示建立該 table 的語法

MySQL 中, CHAR 跟 VARCHAR, 使用的大原則是?

如果確切知道有幾個 character, 就使用 CHAR, 否則則使用 VARCHAR

MySQL 中, CHAR 跟 VARCHAR, 哪個較不會產生 fragmentation?

VARCHAR

MySQL 中, CHAR 跟 VARCHAR, 哪個使用 index 的 performance 較佳?

CHAR

MySQL 中, 當我使用 mysql client select, 有時會因為資料太多而資料欄位沒有排列好, 像下圖一樣, 這時候可以使用哪個 command 來顯示正確的格式?
  • Example:
  • Answer:
    可在最後用 \G 取代 ;
MySQL 中, virtualAs column type 的作用是?

虛擬 column, 內容來自於其他 column 的計算結果(自定義), 比如說, 新增一個 virtualAs column 叫做 full_name, 自定義內容來自於 first_name + last_name

command line 中, 如果 filesystem 的目前登入者跟 MySQL 的 user 是同一個, 那在登入時我還需要使用 mysql -u username -p password 嗎?

不需要, 直接使用 mysql -p 即可

以下的 mysql command 的意思是?
  • Example:

    mysql -u root -p -e "GRANT ALL ON *.* TO 'russell'@'localhost';"
  • Answer:
    賦予 russell, host 為 localhost, 所有資料庫以及 table 的操作權限
    第一個 * 的位置代表資料庫, * 表示全部資料庫
    第二個 * 的位置代表 table, * 表示全部 table

以下的 mysql command 的意思是?
  • Example:

    mysql -u root -p -e "SHOW GRANTS FOR 'russell'@'localhost' \G"
  • Answer:
    顯示 russell 的所有權限

以下的 mysql command 的意思是?
  • Example:

    mysql -u root -p -e "GRANT SELECT ON *.* TO 'russell'@'localhost';"
  • Answer:
    給予 russell user, host 為 localhost, select 所有資料庫所有 table 的權限

以下的 mysql command 的意思是?
  • Example:

    mysql -u root -p -e "GRANT USAGE ON *.*
    TO 'russell'@'localhost'
    IDENTIFIED BY 'Rover#My_1st_Dog&Not_Yours!';"
  • Answer:

    ## 給予存取所有資料庫所有 table 的權限, 這裡是指可存取的 database 以及 table
    ## 但並沒有給予任何實際上的權限, 如 select, update, insert
    mysql -u root -p -e "GRANT USAGE ON *.*
    ## 賦予上面的權限給 user russell, host 為 localhost
    TO 'russell'@'localhost'
    ## 密碼為 Rover#My_1st_Dog&Not_Yours!
    IDENTIFIED BY 'Rover#My_1st_Dog&Not_Yours!';"
以下的 mysql command 的意思是?
  • Example:

    mysqladmin -u root -p flush-privileges
  • Answer:
    當完成一些設定更新後, 下達上面的指令讓設定生效

以下的 mysql command 的意思是?
  • Example:

    mysql -u root -p -e "SELECT User,Host FROM mysql.user;"
  • Answer:
    列出所有的 user 以及 host

以下的 mysql command 的意思是?
  • Example:

    mysql -u root -p -e "SET PASSWORD FOR 'root'@'127.0.0.1' PASSWORD('new_pwd');"
    mysql -u root -p -e "SET PASSWORD FOR 'root'@'localhost' PASSWORD('new_pwd');"
    mysql -u root -p -e "DROP USER 'root'@'%';"
    mysql -u root -p -e "DROP USER ''@'localhost';"
  • Answer:
    1, 2 行給 root 的不同 host 設定新密碼, 儘管 127.0.0.1 等於 localhost, 但也必須分別設定密碼
    3, 'root'@'%' 表示不管從任何 host 都可以 claim 自己是 root, 這樣的設定並不安全, 必須刪除
    4, ''@'localhost' 表示只要是來自 localhost, 任何 username 都被接受, 這樣的設定也不安全, 應該要刪除這樣的 anonymous user

Mysql 中, 如果搜尋的字串是 % 開頭, 那可以用 index 嗎?

不行

Mysql 中, set column type 的特性是?

可指定一個允許輸入值列表, 所有 insert 的值都必須在這列表內才允許輸入, 與 enum 不同之處在於, enum 只允許 insert 一個值, set 允許 insert 零個或多個

如何在已知 root 現有密碼的情況下重新設定 root 密碼?
mysqladmin -u root -p flush-privileges password
Mysql 中, enum column type 的特性是?

可指定一個允許輸入值列表, 所有 insert 的值都必須在這列表內才允許輸入, 與 enum 不同之處在於, enum 只允許 insert 一個值, set 允許 insert 零個或多個

Mysql 中, date, datetime, timestamp, 三種 data type 差異分別是?

date: yyyy-mm-dd
datetime: yyyy-mm-dd hh:mm:ss
timestamp: yyyy-mm-dd hh:mm:ss 除此之外, timestamp 會將收到的時間轉成 UTC, 當 query 此欄位時, 會再從 UTC 轉成目前時區

Mysql 中, float, double, decimal, 由小至大的排列順序為?

float, double, decimal

Mysql 中, 若要精準的儲存一個數字(含小數點), 建議使用哪個 type?

decimal

Mysql 中, double type 有效位數約為?

17 位

Mysql 中, float type 有效位數約為?

7 位數

Mysqld 通常代表什麼?

MySQL Daemon, 通常代表 MySQL Server

以下的 SQL 語法中的意思是?
  • Example:

    CREATE INDEX prod_name_ind
    ON PRODUCTS (prod_name);
  • Answer:
    建立一個 index, 名為 prod_name_ind, table 為 PRODUCTS, 欄位為 prod_name

以下的 SQL 語法中的意思是?
  • Example:

    BEGIN TRANSACTION
    INSERT INTO Customers(cust_id, cust_name)
    VALUES('1000000010', 'Toys Emporium');
    SAVE TRANSACTION StartOrder;
    INSERT INTO Orders(order_num, order_date, cust_id)
    VALUES(20100,'2001/12/1','1000000010');
    IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
    INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20100, 1, 'BR01', 100, 5.49);
    IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
    INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20100, 2, 'BR03', 100, 10.99);
    IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
    COMMIT TRANSACTION
  • Answer:
    在第一個 insert 建立一個 savepoint StartOrder, 如果之後的語法失敗, 回到 savepoint

以下的 SQL 語法中的意思是?
  • Example:

    CREATE VIEW ProductCustomers AS
    SELECT cust_name, cust_contact, prod_id
    FROM Customers, Orders, OrderItems
    WHERE Customers.cust_id = Orders.cust_id
    AND OrderItems.order_num = Orders.order_num;
  • Answer:
    建立一個 VIEW, 為一張虛擬的 table, 可藉此簡單化省略重複複雜 SQL 語法

以下的 SQL 語法中的意思是?
  • Example:

    SELECT *
    INTO CustCopy
    FROM Customers;
  • Answer:
    從 Customers table 撈出所有 record, 再 insert 到 CustCopy table

以下的 SQL 語法中的意思是?
  • Example:

    INSERT INTO Customers(cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country)
    SELECT cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country
    FROM CustNew;
  • Answer:
    從 CustNew table 中 select 出 record, 在 insert 進 Customers table

以下的 SQL 語法中的意思是?
  • Example:

    SELECT cust_name, cust_contact, cust_email
    FROM Customers
    WHERE cust_state IN ('IL','IN','MI')
    UNION
    SELECT cust_name, cust_contact, cust_email
    FROM Customers
    WHERE cust_name = 'Fun4All'
    ORDER BY cust_name, cust_contact;
  • Answer:
    只在最後一個 SELECT 使用 ORDER BY, 因為 UNION 會將它用來排序所有 SELECT 語句返回的結果

以下的 SQL 語法中的意思是?
  • Example:

    SELECT cust_name, cust_contact, cust_email 
    FROM Customers
    WHERE cust_state IN ('IL','IN','MI')
    UNION ALL
    SELECT cust_name, cust_contact, cust_email
    FROM Customers
    WHERE cust_name = 'Fun4All';
  • Answer:
    使用 UNION 語法同時執行兩個 query, UNION 默認取消重複的 record, ALL 表示不默認取消

以下的 SQL 語法中的意思是?
  • Example:

    SELECT cust_name, cust_contact
    FROM Customers AS C, Orders AS O, OrderItems AS OI
    WHERE C.cust_id = O.cust_id
    AND OI.order_num = O.order_num
    AND prod_id = 'RGAN01';
  • Answer:
    使用 AS 來縮短 SQL 語法

以下的 SQL 語法中的意思是?
  • Example:

    SELECT prod_name, vend_name, prod_price, quantity
    FROM OrderItems, Products, Vendors
    WHERE Products.vend_id = Vendors.vend_id
    AND OrderItems.prod_id = Products.prod_id
    AND order_num = 20007;
  • Answer:
    從三個 table 中取出指定的 column, 並指定連結

以下的 SQL 語法會取出多少行的 record?
  • Example:

    SELECT vend_name, prod_name, prod_price
    FROM Vendors, Products;
  • Answer:
    因為沒有指定關聯, 所以共會取出 Vendors 與 Products 行數的相乘

以下的 SQL 語法中的意思是?
  • Example:

    SELECT cust_name, 
    cust_state,
    (SELECT COUNT(*)
    FROM Orders
    WHERE Orders.cust_id = Customers.cust_id) AS orders
    FROM Customers
    ORDER BY cust_name;
  • Answer:
    利用子查詢取得 orders 欄位, 即在 orders table 中, cust_id 跟 Customers table 中的 cust_id 相同

以下的 SQL 語法中, 作為子查詢, 可以 select 多個 column 嗎?
  • Example:

    SELECT cust_id
    FROM Orders
    WHERE order_num IN (SELECT order_num
    FROM OrderItems
    WHERE prod_id = 'RGAN01');
  • Answer:
    不行

以下的 SQL 語法的意思是?
  • Example:

    SELECT cust_id
    FROM Orders
    WHERE order_num IN (SELECT order_num
    FROM OrderItems
    WHERE prod_id = 'RGAN01');
  • Answer:
    從子查詢得到的結果來 filter

下面的 select 子句的排列順序是?
  • Example
    SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
  • Answer:
SQL 語法中, WHERE 跟 HAVING 差別是?

where 過濾行
having 過濾分組

SQL 語法中, count(*) 會否忽略 NULL 值?

不會

SQL 語法中, 當使用 + 把不同 column 的值串在一起, 卻發現有多餘的空白如下 example, 在 example 2 當中, 該怎麼去掉這些空白呢?
  • Example output


    -----------------------------------------------------------
    Bear Emporium (USA )
    Bears R Us (USA )
    Doll House Inc. (USA )
    Fun and Games (England )
    Furball Inc. (USA )
    Jouets et ours (France )
  • Example 2

    SELECT RTRIM(vend_name) + ' (' + 這裡是?(vend_country) + ')'
    FROM Vendors
    ORDER BY vend_name;
  • Answer

    SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
    FROM Vendors
    ORDER BY vend_name;
SQL 語法中, 如果要把兩個不同 column 的 value 串在一起成一個字串輸出, 如下 example, 該使用什麼符號??
  • Example

    SELECT vend_name 這裡是? ' (' 這裡是? vend_country 這裡是? ')'
    FROM Vendors
    ORDER BY vend_name;
  • Answer

    SELECT vend_name + ' (' + vend_country + ')'
    FROM Vendors
    ORDER BY vend_name;
SQL 語法中, wildcard 是否會耗費比較多的時間?

會的

SQL 語法中, 當使用 wildcard 時, 最好將語法置於開頭處或結尾處?

結尾處

SQL 語法中, 假如我要找出所有名字以 J 或 M 起頭的聯絡人, 在以下的 example 中, 該使用哪個 wildcard?
  • Example

    SELECT cust_contact
    FROM Customers
    WHERE cust_contact LIKE '這裡是?%'
    ORDER BY cust_contact;
  • Answer

    SELECT cust_contact
    FROM Customers
    WHERE cust_contact LIKE '[JM]%'
    ORDER BY cust_contact;
SQL 語法中, % 代表多字 wildcard, 如果我要單個字的 wildcard, 要使用哪個符號?

_, 是一個 word, 不是 character

SQL 語法中, 以下 example 中的語法會撈出 value 為 null 的 row 嗎?
  • Example:

    SELECT prod_name
    FROM Products
    WHERE prod_name LIKE '%';
  • Answer:
    不會

以下的 SQL 語法是什麼意思?
  • Example:

    SELECT prod_name
    FROM Products
    LIMIT 5 OFFSET 5;
  • Answer:
    抵銷 5 行, 也就是從第 6 行開始撈
    撈 5 行資料

SQL 語法中, 有區分大小寫嗎?

SQL 語法中, inner join 跟 join 差在哪?

一樣

SQL 語法中, left outer join 跟 left join 差在哪?

一樣

SQL 語法中, right outer join 跟 right join 差在哪?

一樣

SQL 語法中, join 語法的用途是?

同時撈出兩張表的資料

SQL 語法中, left join 跟 right join 語法的差別是?

left join 除了 join 兩張表有相關的資料外, 還會把左邊表的所有不相關資料也撈出來, right join 則是會把右邊表的所有不相關資料取出

以下的 MySQL example command 的意思是?
  • Example:

    show variables like 'validate_password%';
  • Answer:
    顯示密碼驗證相關訊息, 如下

    # validate_password.check_user_name	ON
    # validate_password.dictionary_file
    # validate_password.length 8
    # validate_password.mixed_case_count 1
    # validate_password.number_count 1
    # validate_password.policy MEDIUM
    # validate_password.special_char_count 1
以下的 MySQL example command 的意思是?
  • Example:

    SELECT user,authentication_string,plugin,host FROM mysql.user;
  • Answer:
    user: username
    authentication_string: 就是 password
    plugin: 這邊是 authentication plugin, caching_sha2_password 可在 memory cache 之前使用過的 user, 增進效能
    host: 該 user 的 host, username + host 才是一個完整的 user

以下的 MySQL example command 的意思是?
  • Example:

    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
    FLUSH PRIVILEGES;
  • Answer:
    更改 root 密碼

以下的 MySQL example command 的意思是?
  • Example:

    set global validate_password_length=3;
  • Answer:
    修改密碼長度規則

以下的 MySQL example command 的意思是?
  • Example:

    set global validate_password_policy=0;
  • Answer:
    修改密碼驗證原則

以下的 MySQL example command 的意思是?
  • Example:

    CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  • Answer:
    建立資料庫 mydatabase, 設定 character set 為 utf8mb4, collate 為 utf8mb4_unicode_ci

以下的 MySQL example command 的意思是?
  • Example:

    mysql -uroot -pYourPassword -Bse "use test; delete from testTable;"
  • Answer:
    從 shell 不進入 MySQL client 直接下達 command

以下的 MySQL example command 的意思是?
  • Example:

    set global log_timestamps = 'system';
  • Answer:
    更改 log 的時區為系統時間

以下的 MySQL example command 的意思是?
  • Example:

    DROP DATABASE `databaseName`;
  • Answer:
    刪除資料庫


# 參考書目

手把手教你理解並建立 GCP 平衡負載 Gitlab 學習筆記

留言

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×