MySQL 的 string type 和 storage size 關係

本文有錯請指正。


以下 storage size 指的是實際儲存的空間,不是用 select length() 拿出來的資訊。

In the following table, M represents the declared column length in characters for non-binary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.

型態: CHAR(M)
需要的空間: M × w bytes, 0 <= M <= 255, M 是定義好的字數,至多存 255 個字,w 是目前使用編碼的 byte 數,例如 utf8 就是 3 bytes (或 2 bytes) 。

型態: BINARY(M)
需要的空間: M bytes, 0 <= M <= 255

型態: VARCHAR(M), VARBINARY(M)
需要的空間:
L + 1 bytes if column values require 0 − 255 bytes,
L + 2 bytes if values may require more than 255 bytes

型態: TINYBLOB, TINYTEXT
需要的空間: L + 1 bytes, 當 L < 2^8

型態: BLOB, TEXT
需要的空間: L + 2 bytes, 當 L < 2^16

型態: MEDIUMBLOB, MEDIUMTEXT
需要的空間: L + 3 bytes, 當 L < 2^24

型態: LONGBLOB, LONGTEXT
需要的空間: L + 4 bytes, 當 L < 2^32

型態: ENUM(‘value1′,’value2’,…)
需要的空間: 1 or 2 bytes, 看有幾個 ENUM 的值而定,最多 65,535 個。

型態: SET(‘value1′,’value2’,…)
需要的空間: 1, 2, 3, 4, or 8 bytes, 看 SET 內有多少 member ,最多 64 個。

SQL Group By 可能發生的誤用

最近有個需求,要一些資料 Group 起來,不過後來測試發現會有欄位資料拿錯的情況,趕緊去請教公司的 DBA 大神之後,發現是我的 SQL 下錯了。

假設有一批水果資料,攤平之後是這樣:

sn         item    attr1    attr2       attr3        created_date
1          蕃茄        1         2          3         2015-01-15
2          蘋果        1         1          1         2015-01-27
3          蘋果        2         3          4         2015-01-28
4          蘋果        3         3          3         2015-01-29
5          香蕉        1         1          1         2015-01-27
6          香蕉        2         2          2         2015-01-28
7          橘子        1         1          1         2015-01-28
9          蘋果        5         4          0         2015-01-25
11         蘋果        3         2          3         2015-01-20

我想要拿出每種水果最新一筆的資料,所以使用以下語法:

created_date 欄位型態是時間,所以可以直接用 MAX 運算。

sn         item    attr1    attr2       attr3       created_date
1          蕃茄        1         2          3         2015-01-15
5          香蕉        1         1          1         2015-01-28
7          橘子        1         1          1         2015-01-28
11         蘋果        3         2          3         2015-01-29

但拿出來之後,檢查發現「香蕉」欄位除了日期以外,其他資料都錯了,而「蘋果」的 serial 和 attr2 兩個欄位也錯了,實際上資料庫並沒有照我預期的方式給我資料。

後來請教 DBA 大神之後,發現我語法應該改成這樣:

先用 sub query 拿出我要的條件,然後用 WHERE IN 再去把資料撈出來

sn         item    attr1    attr2       attr3        created_date
1          蕃茄        1         2          3         2015-01-15
6          香蕉        2         2          2         2015-01-28
7          橘子        1         1          1         2015-01-28
4          蘋果        3         3          3         2015-01-29

這次就對了。

MySQL unsigned INT 的問題 – 以噗浪為例

在 MySQL 開一個 INT 欄位的時候,有時候我們會把欄位屬性設定為 Unsigned ,這樣可以儲存更多的正整數。

以 Signed 為例,可以儲存 -2147483648 to 2147483647 而以 Unsigned 為例,可以儲存 0 to 4294967295 不過在操作這類欄位的時候,必須要小心,以避免刪過頭,反而變成補血補滿。
Continue reading “MySQL unsigned INT 的問題 – 以噗浪為例”

Debian Squeeze 安裝 MySQL HandlerSocket Plugin

去年 10 月 20 YOSHINORI MATSUNOBU 寫了 Using MySQL as a NoSQL – A story for exceeding 750,000 qps on a commodity server 這篇文章,裡面介紹了 DeNA Co.,Ltd. 發表的 HandlerSocket-Plugin-for-MySQL,好處在原網站有講到很多,最大的優勢是速度比傳統的用 SQL 查詢 MySQL 還要快了七倍之多 (MySQL via SQL = 105,000 qps, MySQL via HandlerSocket = 750,000 qps).
Continue reading “Debian Squeeze 安裝 MySQL HandlerSocket Plugin”

將頻繁的查詢改放在 memcached 內

最近改寫了一些以前的程式,目的是把一些不太重要,過期就用不到的東西丟到 memcached 裡面去放著。趁著週五的晚上有空,把新版的程式丟到伺服器上測試。跑了一段時間後,看 MySQL 的使用狀況,真是令人賞心悅目。

圖中大幅降低的值是 MySQL Server Status 的 Questions ,這個值的定義是 MySQL 執行過的 SQL 敘述次數,這個數據的定義可以看: 5.1.6. Server Status Variables#statvar_Questions

也可以看到 open table / file 次數也降低一半

Open_tables 表示 被開啟的資料表數目 (The number of tables that are open.), Opened_files 則是被 MySQL 內部函數 my_open() 打開的檔案個數,這兩個數據的定義可以看: 5.1.6. Server Status Variables#statvar_Open_tables

Oracle 中限制資料回傳筆數的方法

用慣了 MySQL 大概都知道一個非常實用的語法: LIMIT ,這個語法可以限制回傳結果的資料筆數,以及從第幾筆查詢結果開始回傳,最廣為人知的應用大概就是分頁功能,但在 Oracle 裡面並沒有 LIMIT 這個語法,取而代之的是 ROWNUM 。
Continue reading “Oracle 中限制資料回傳筆數的方法”

在 Ubuntu 9.10 x86_64 編譯 Oracle Client 的 php extension

先安裝 apt-get install php-pear php5-dev php5-cli libaio1 build-essential 以及其他必要套件。

到 Oracle 官方網站下載 Oracle Database Instant Client,在下載頁面尋找適合的平台,進入之後下載 Basic 和 SDK 兩個 package 。
Continue reading “在 Ubuntu 9.10 x86_64 編譯 Oracle Client 的 php extension”