Mysql设计与查询的关键注意事项

喜欢ヅ旅行 2023-02-28 11:28 1阅读 0赞

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;

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