小 縮小字型大小。 中 重設字型大小。 大 放大字型大小。

標籤: MySQL

讓 MySQL 預設使用特定的 character set

Posted on By 日落 在〈讓 MySQL 預設使用特定的 character set〉中尚無留言

MySQL 支援多個不同的 character set，像是 latin1、utf8 等等，但是只要在規劃、連線時沒有做好設定，就很容易對應到錯誤的 character set 導致出現亂碼。

自己從 gslin 和 Percona、Study-Area 那邊整理出一些比較保險的設定方法，可以避免之後在維護時發生錯誤：

[client]
default-character-set = utf8mb4

[mysqld]
default-storage-engine         = InnoDB
character-set-server           = utf8mb4
collation-server               = utf8mb4_unicode_ci
init_connect='SET collation_connection = utf8mb4_unicode_ci'
init_connect='SET NAMES utf8mb4'

備註：

  • 以上設定檔適用於 MySQL / Percona server 5.7，不同版本有不同的 config keyword
  • 上面的範例為 utf8mb4，可自行調整成自己需要的 character set
  • 就算 MySQL server 有這些設定以後，連線到 MySQL 的程式也需要注意編碼設定，否則還是會發生編碼錯誤
Tags:

讓 MySQL 在查詢時區分英文大小寫

Posted on By 日落 在〈讓 MySQL 在查詢時區分英文大小寫〉中尚無留言

今天又遇到相同的問題，解法有很多種，可以從 SQL 下手，也可以在 table schema 就先做好設定。

SQL 強制區分大小寫

假設原本的 SQL 為：
SELECT * FROM users WHERE name = ‘john’ ;

這樣會撈出「John」、「john」等結果。那麼可以要求 MySQL 使用 binary 的辨識方法去做搜尋：

SELECT * FROM users WHERE binary name = 'johhn';

這樣一來，查詢時 name 欄位就會區分大小寫來做查詢。

修改 table schema

如果在設計 table 時，就確定查詢一定要區分大小寫時，可以在 create table 就先將欄位設定好：

CREATE TABLE users (
    name varchar(100) binary
)

這樣之後下 query 時，只要遇到 name 欄位，就自動會區分大小寫。

修改 collation type

一般常用的 collation 是「utf8_general_ci」，該 collation 最後面的「ci」其實是「Case Insensitive」的意思，也就是不區分大小寫。

如果要讓該 table 的所有欄位都區分大小寫，可以將 collation 的 postfix 改為「cs」或是「bin」，例如：

CREATE TABLE user (
    name varchar(100)
) COLLATE utf8_general_bin ;

以上三個區分大小寫的方法 scope 差異頗大的，可以挑比較適合當下情況的方法來使用。

Tags:,

增加 MySQL insert 速度的撇步

Posted on By 日落 在〈增加 MySQL insert 速度的撇步〉中尚無留言

一次要新增大批資料到 MySQL 時，就會 MySQL 吃 CPU 又卡 I/O wait，這個時候如果沒時間吃下午茶的話實在會有點不爽 (?)

假設今天有個 table 長這個樣子：

mysql> desc users;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| id           | bigint(20) unsigned | NO   | PRI | NULL    |       |
| email        | varchar(200)        | YES  |     | NULL    |       |
| display_name | varchar(500)        | YES  |     | NULL    |       |
| reg_date     | int(10) unsigned    | YES  |     | NULL    |       |
| last_login   | int(10) unsigned    | YES  |     | NULL    |       |
| delete_at    | int(10) unsigned    | YES  |     | NULL    |       |
+--------------+---------------------+------+-----+---------+-------+

登入、使用者管理等動作，我們會為以下幾個欄位加上 index 以加快查詢速度：

  • email
  • reg_date
  • last_login
  • delete_at

這個時候我們新增一筆資料時，MySQL 大概會做幾個動作：

  1. 新增一個 row
  2. 更新 index
    1. 更新 email index
    2. 更新 reg_date index
    3. 更新 last_login index
    4. 更新 delete_at index

所以如果只是把新增一筆資料，事情最多的不是建立一個 record，而是更新所有的 index。假設要新增的資料有 10000 筆，新增資料的 I/O 就當作 10000 次好了，更新 index 就另外再增加 40000 次 I/O。

若這 10000 筆資料是一次處理完，且不打算中斷，是否有發法可以減少 index 處理的次數？答案是 transaction。

當 10000 個 insertion 放在同一個 transaction 時，MySQL 會在 insertion 確認成功以後再來一次 update index。也就是說原本 update index 跑 40000 次，現在只需要跑 4 次。

由於 transaction 會造成 lock，新增資料前可以先試試看一個 transaction 要新增幾個 record，lock 的時間才不會影響到其他服務的運作。

ref:

Tags:

MySQL pager

Posted on By 日落 在〈MySQL pager〉中有 1 則留言

MySQL 中的 query 結果太多時，超過螢幕高度就只能看到最後方的資料，要靠 terminal cache 才能看到前半部。

但其實 MySQL 有 pager 功能：

mysql> pager
Default pager wasn't set, using stdout.

把 pager 設定成 less：

mysql> pager less
PAGER set to 'less'

這樣一來，查詢結果就會送到 less 中，可以按照自己的閱讀速度捲動閱讀。

ps. pager 好像是綁 session，再次登入又要重新設定

ref:
Neat tricks for the MySQL command-line pager

Tags: