database | Calos's Blog

Calos 2018-06-14 2018-09-11 Database / MariaDB / MySQL

[MariaDB/MySQL] 建立連線帳號與指派權限

建立帳號，其中「myaccount」是帳號，「mypassword」是密碼，主機為 localhost。

CREATE USER 'myaccount'@'localhost' IDENTIFIED BY 'mypassword';

權限設定的指令結構如下（MariaDB 10.2）：

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user  [IDENTIFIED [BY [PASSWORD] 'password']
        |{VIA|WITH} plugin_name 
           [{USING|AS} 'plugin_option']]
        [, user [IDENTIFIED [BY [PASSWORD] 'password']
        |{VIA|WITH} plugin_name] 
           [{USING|AS} 'plugin_option']]
    user_options...

GRANT PROXY ON user_specification
    TO user_specification [, user_specification] ...
    [WITH GRANT OPTION]

user_options:
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH with_option [with_option] ...]

object_type:
    TABLE
  | FUNCTION
  | PROCEDURE

priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
  | MAX_STATEMENT_TIME time

tls_option:
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'

database / mariadb / mysql

Calos 2018-05-31 2018-09-11 Database / MariaDB / MySQL

[MariaDB/MySQL] 在 Update 子查詢中使用 Limit 限制影響範圍

一般情況下，我們可以很簡單的利用 Limit 語法在進行資料異動中限制影響範圍。

UPDATE `my_table` SET `my_col` = 'data' LIMIT 2;

但是涉及使用子查詢的情境時，這招便不管用了。

例如今天資料長這樣：

+--------+--------+
| number | my_col |
+--------+--------+
|      1 | a      |
|      1 | a      |
|      2 | b      |
|      2 | b      |
|      3 | c      |
|      3 | c      |
+--------+--------+

我想要 Update 前面兩種資料，也就是要下 Limit 2，但是 number 一樣的話就要一起 Update，所以 number 為 1 和 2 的 record 應該都要 Update，所以預期應該要有 4 筆 record 被 Update。直覺利用 Sub-Query 應該可以做到：

UPDATE `my_table` SET `my_col` = 'data' WHERE `number` IN (
    SELECT DISTINCT `number` FROM `my_table` LIMIT 2
);

但是如果在 MariaDB/MySQL 5.5 或之後的版本執行時應該會得到 [Err] 1235 - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'。

那這樣要怎麼辦呢？工具是死的，人是活的，Stackoverflow 就有人想到辦法繞過去了。我們把 Limit 語法放到第三層子查詢（子查詢中的子查詢）就好了！

UPDATE `my_table` SET `my_col` = 'data' WHERE `number` IN (
    SELECT `number` FROM (
        SELECT DISTINCT `number` FROM `my_table` LIMIT 2
    ) as `sub_query_2`
);

Reference: MySQL – UPDATE query with LIMIT – Stack Overflow

database / limit / mariadb / mysql / sub-query / 子查詢

Calos 2017-08-21 2018-09-11 Database / MariaDB / MySQL

[MariaDB/MySQL] ERROR 1118 (42000): Row size too large (> 8126)

今天在匯入資料庫時出現錯誤，導致匯入中斷

ERROR 1118 (42000) at line 437: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

造成的主因是設定檔中啟用了 innodb_strict_mode。

在 MariaDB 10.2.2，或是 MySQL 5.7.7 以上的版本預設會開啟，這裡使用的伺服器是 MariaDB 10.2.8，所以在匯入的 SQL 檔單中有單行超過 8126 個字元的話，在 innodb_strict_mode 開啟的情況下會觸發錯誤，並中斷執行。

一般情況下建議是開啟的，因為相較之下 run-time error 會更頭痛，不過這裡只是單純要匯入資料，所以暫時先關閉後再開啟。

編輯 /etc/mysql/my.cnf，並將 innodb_strict_mode 設為 0，重新啟動服務後即可。

References:
Stack Overflow: MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB
Stack Overflow: Change limit for “Mysql Row size too large
MariaDB: XtraDB/InnoDB Strict Mode
MySQL: MySQL Glossary

database / mariadb / mysql

Calos 2017-02-06 2018-09-11 Database / MariaDB / MySQL

[MariaDB/MySQL] mysqldump include/exclude (ignore) specific table

備份資料庫其中一種方式是使用 mysqldump 指令，例如我要備份一個名為db1的資料庫：

mysqldump -u admin -p db1
# 參數說明
# -u: 使用者名稱
# -p: 密碼，指令enter後會要求輸入密碼，也可以直接將密碼連接在後面，如密碼為123456的話，參數改成 '-p123456' 後指令會直接執行，不會再次要求輸入密碼

若只要備份特定的資料表時，有三種方式

排除/忽略特定資料表 (Exclude / Ignore)
使用參數 --ignore-table 去排除，需完整名稱 (即 database.table)；多個 table 無法使用逗點 (comma) 串接，需個別指定。適用於排除少數資料表。
```
mysqldump -u root -p123456 myDB --ignore-table=myDB.logs --ignore-table=myDB.users > dump.sql
```
指定資料表名稱 (Include)
在資料庫名稱後面指定資料表名稱，可指定多個資料表；無法使用 * 去指定特殊的 prefix / suffix；適用於匯出少數資料表。
```
mysqldump -u root -p123456 myDB table_1 table_2 table_3 > dump.sql
```
包含特定資料表前綴/後綴或特定 pattern 的資料表 (Include prefix / suffix / pattern)
利用 sub command 在內層執行 SQL 先取得指定資料表名稱後，再執行外層的 mysqldump ，適用於資料表數量眾多，需匯出指定資料表也較多的資料庫。
```
mysqldump -u root -p123456 myDB  $(mysql -u root -p123456 myDB -Bse "SHOW TABLES LIKE 'my_table_%'") > dump.sql
```
一般情況下用 LIKE 語法就夠了，若情況特殊，用 REGEXP 語法去撈也是可行的，需注意 WHERE 的欄位是 `Tables_in_資料庫名稱` 。
```
mysqldump -u root -p123456 myDB  $(mysql -u root -p123456 myDB -Bse "SHOW TABLES WHERE `Tables_in_myDB` REGEXP 'table_[0-9]+_[A-Za-z]+'") > dump.sql
```

Reference: http://stackoverflow.com/a/26514699/7521686

backup / database / export / mariadb / mysql / mysqldump / pattern

Calos 2015-05-15 2018-09-11 Database / MariaDB / MySQL

[MySQL] 在同一張資料表進行複製資料的操作

最近剛好碰到需要在同一個table內copy資料的情況
由於先dump出來再操作實在是覺得有點麻煩又有點蠢
想到應該可以直接下SQL來完成這件事情
於是稍微研究了一下
基本上只要避開primary column即可完成
例如：有一張資料表叫做table_1，表結構有三個欄位，分別是col_1, col_2, col_3，其中col_1為primary column，然後使用下列SQL語法：

INSERT INTO `table_1`(`col_2`, `col_3`) SELECT (`col_2`, `col_3`) FROM `table_1` WHERE `col_2` > 100

下了這行SQL後，會在資料表複製並寫入現在col_2值大於100的資料

clone / copy / database / mariadb / mysql / table