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字节）．