Mysql設計與查詢的關鍵注意事項

1. 對於varchar欄位的值要考慮是否區分大小寫，即欄位值大小敏感性

 mysql 大小寫遵循collate規則：
 *_bin: 表示的是binary case sensitive collation，區分大小寫，#mysql對外提供了該方法
 *_cs: case sensitive collation，區分大小寫              #mysql暫未對外提供該方法
 *_ci: case insensitive collation，不區分大小寫        #mysql對外提供了該方法

如果資料庫中沒有欄位需要考慮大小寫問題，完全可以跳過本節；如果關心大小寫問題，則可以從建表和查詢2個層面來解決問題。

查詢

將查詢條件用binary()括起來。 比如： 
select * from TableA where binary columnA ='aaa';

建表

即在建立表的時候對欄位設定大小寫敏感的校驗規則

修改該欄位的collation 為 binary比如：
ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME VARCHAR(50) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;

上面的藍色BINARY指出使用大小寫敏感，紅色CHARACTER SET utf8指明資料欄位使用的編碼集(實際是不需要設定的，因為書它是根據資料庫來的，即使預設也是utf8), 綠色COLLATE utf8_bin指明COLLATE 規則具體採用哪種大小寫敏感的方式（一種utf8_bin，一種utf8_cs, 實際中只有utf8_bin在用，所以也可以不新增預設就是utf8_bin）。即一般情況下，可以進一步簡化為，如下：

ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME VARCHAR(50) BINARY  DEFAULT NULL;

or

ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME VARCHAR(50) COLLATE utf8_bin DEFAULT NULL;

支援大小寫敏感的欄位型別包括CHAR、VARCHAR和TEXT型別。

MySql在Linux下，資料庫名、表名、列名、別名大小寫規則是這樣的：
1. 資料庫名和表名是嚴格區分大小寫的
2. 表的別名嚴格區分小大寫
3. 列名與列的別名在所有情況下均是忽略小大寫的
4. 變數名也是嚴格區分大小寫的

2.是否需要支援表情符號

mysql預設的字符集編碼方式是utf8,已經可以滿足大部分場景，也僅是大部分場景，對於一個生產環境的庫顯然是不夠的。MySQL在5.5.3之後增加了這個utf8mb4的編碼，mb4就是most bytes 4的意思，專門用來相容四位元組的unicode。
utf8mb4是utf8的超集，除了將編碼改為utf8mb4外不需要做其他轉換。為了節省空間，一般情況下使用utf8也就夠了。

此時，utf8_bin的改成utf8mb4_bin

原理分析

utf8能夠存下大部分中文漢字,那為什麼還要使用utf8mb4呢? 原來mysql支援的 utf8 編碼最大字元長度為 3 位元組，如果遇到 4 位元組的寬字元就會插入異常了。
三個位元組的 UTF-8 最大能編碼的 Unicode 字元是 0xffff，也就是 Unicode 中的基本多文種平面（BMP）。
也就是說，任何不在基本多文字平面的 Unicode字元，都無法使用 Mysql 的 utf8 字符集儲存。
包括 Emoji 表情（Emoji 是一種特殊的 Unicode 編碼，常見於 ios 和 android 手機上），和很多不常用的漢字，以及任何新增的 Unicode 字元等等

為了獲取更好的相容性，應該總是使用 utf8mb4 而非 utf8. 對於 CHAR 型別資料，utf8mb4 會多消耗一些空間，根據 Mysql 官方建議，使用 VARCHAR 替代 CHAR。

3.整型資料型別的選擇以及設定大小的影響

1.BIT[M]
位欄位型別，M表示每個值的位數，範圍從1到64，如果M被忽略，預設為1
2.TINYINT[(M)] [UNSIGNED] [ZEROFILL]  M預設為4
很小的整數。帶符號的範圍是-128到127。無符號的範圍是0到255。
3. BOOL，BOOLEAN
是TINYINT(1)的同義詞。zero值被視為假。非zero值視為真。
4.SMALLINT[(M)] [UNSIGNED] [ZEROFILL] M預設為6
小的整數。帶符號的範圍是-32768到32767。無符號的範圍是0到65535。
5.MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] M預設為9
中等大小的整數。帶符號的範圍是-8388608到8388607。無符號的範圍是0到16777215。
6. INT[(M)] [UNSIGNED] [ZEROFILL]   M預設為11
普通大小的整數。帶符號的範圍是-2147483648到2147483647。無符號的範圍是0到4294967295。
7.BIGINT[(M)] [UNSIGNED] [ZEROFILL] M預設為20
大整數。帶符號的範圍是-9223372036854775808到9223372036854775807。無符號的範圍是0到18446744073709551615。

int(M) zerofill，加上zerofill後M才表現出有點點效果，比如 int(3) zerofill,你插入到資料庫裡的是10,則實際插入為010,也就是在前面補充加了一個0.如果int(3)和int(10)不加zerofill,則它們沒有什麼區別．Ｍ不是用來限制int個數的．int(M)的最大值和最小值與undesigned有關。

mysql> create table t (t int(3) zerofill);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t set t = 10;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+——+
| t |
+——+
| 010 |
+——+
1 row in set (0.11 sec)

Zerofill with default width, the same as int(10):

mysql> create table t (t int zerofill);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t set t = 10;
Query OK, 1 row affected (0.02 sec)

mysql> select * from t;
+————+
| t |
+————+
| 0000000010 |
+————+
1 row in set (0.08 sec)

Without zerofill:

mysql> create table t (t int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t set t = 10;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+——+
| t |
+——+
| 10 |
+——+

1 row in set (0.00 sec)

4.char與varchar在實際儲存中的選擇和應用

char是一種固定長度的型別，varchar則是一種可變長度的型別，它們的區別是：
char(M)型別的資料列裡，每個值都佔用M個位元組，如果某個長度小於M，MySQL就會在它的右邊用空格字元補足．
在檢索操作中那些填補出來的空格字元將被去掉）
在varchar(M)型別的資料列裡，每個值只佔用剛好夠用的位元組再加上一個用來記錄其長度的位元組（即總長度為L+1位元組）．