技巧

JSON Array 轉 Table

《參考：Convert JSON array in MySQL to rows - Stack Overflow》

SELECT *
FROM fortop.mold_info,
	JSON_TABLE(
		term_model,
		"$[*]"
		COLUMNS (
			value VARCHAR(30) PATH "$"
		)
	) step

更新符合條件的資料排序

參考 [MySQL] 讓使用者可手動排序(sort_key)的資料庫table規劃 « YuTin's Blog

SET @sort = 0;                      -- 自訂變數用來遞增

UPDATE product_process_step
SET step = (@sort:= @sort + 1)      -- 欲排序的欄位 step
WHERE product_process_id = '20-C6'  -- 設定條件
ORDER BY FIELD (id, 5, 8, 9, 10)    -- 依據指定欄位 id 為 5, 8, 9, 10 的順序排序

記錄不存在 INSERT，存在 UPDATE

使用 ON DUPLICATE KEY UPDATE：

INSERT INTO product_process (id, modify_date, base_jig_id)
VALUES('11*11-1335-01-6B', NOW(), '11-5193')
    ON DUPLICATE KEY UPDATE modify_date = NOW(), base_jig_id = '11-5193'

依序取得資料表所有欄位名稱

使用 SELECT 取得某一資料表所有欄位名稱，這在合併多個資料表時 SELECT 必須自行指定要顯示的欄位名稱很多個時能方便用來直接複製：

SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE table_name = 'your_table_name'
    AND table_schema = 'your_db_name'
ORDER BY ORDINAL_POSITION

設定

INSERT 和 UPDATE 資料容量限制

查看 max_allowed_packet 預設允許的容量，如下為 1048576 / 1024 / 1024 = 1M：

mysql -u root -p

Enter password:

# ... 中間省略 ...

MariaDB [(none)]> SHOW variables LIKE 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

MariaDB [(none)]> exit
Bye

以 CentOS 7 為例，編輯 MariaDB / MySQL 設定檔，新增 max_allowed_packet 設定允許的容量：

vim /etc/my.cnf

[mysqld]

# ... 中間省略 ...

# 設定 INSERT 和 UPDATE 資料容量限制
max_allowed_packet=10M

[mysqld_safe]

# ... 以下省略 ...

重啟 MariaDB (mariadb) / MySQL (mysqld) 服務：

systemctl restart mariadb

查看 max_allowed_packet 修改後允許的容量，如下為 10485760 / 1024 / 1024 = 10M：

mysql -u root -p

Enter password:

#... 中間省略 ...

MariaDB [(none)]> SHOW variables LIKE 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 10485760 |
+--------------------+---------+
1 row in set (0.00 sec)

MariaDB [(none)]> exit
Bye

錯誤問題排除

查詢比對字符集不一致錯誤

有時如下查詢不同資料庫 table 時會出現下述 error：

#1267 - Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

可使用 COLLATE 指定為相同字符集：

SELECT
    line_assign.id,
    line_assign.primary_product,
    process_item.process_id,
    process_item.process_name
FROM line_assign
    LEFT JOIN fortop.process_item AS process_item
        ON process_item.process_id = line_assign.process_id COLLATE utf8mb4_unicode_ci

新增使用者或修改密碼錯誤

1819 - Your password does not satisfy the current policy requirements

mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 81530492
Server version: 8.0.17 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'a51132233'@'%' IDENTIFIED BY 'a1b2';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

查看 MySQL 密碼安全設定資訊：

mysql> SHOW VARIABLES LIKE "%validate%";
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| 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      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

密碼政策可設定密碼政策及進行密碼的測試如下表：

validate_password.policy

政策	進行測試
0 or LOW	只驗證長度
1 or MEDIUM	驗證長度、數字、字母大/小寫及特殊字符
2 or STRONG	驗證長度、數字、字母/大小寫、特殊字符及字典文件

暫時性設定 (重啟 MySQL 或系統會恢復預設值)

設定密碼長度：

mysql> SET GLOBAL validate_password.length=4;
Query OK, 0 rows affected (0.01 sec)

設定密碼政策：

mysql> SET GLOBAL validate_password.policy=0;
Query OK, 0 rows affected (0.00 sec)

查看 MySQL 密碼安全設定後資訊：

mysql> SHOW VARIABLES LIKE "%validate%";
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 4     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 1     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)

永久性設定

vim /etc/my.cnf

[mysqld]

#
# 密碼安全設定
# https://dev.mysql.com/doc/refman/8.0/en/validate-password-options-variables.html
#
--validate-password-length=4    # 密碼長度 (預設 8)
--validate-password-policy=0    # 密碼政策 (預設 1)