Mysql文本字符串类型实战

逃离我推掉我的手 2021-07-24 17:02 346阅读 0赞

**一 下面将不同字符串保存到CHAR(4)和VARCHAR(4)列，说明CHAR和VARCHAR之间的差别**

CHAR(4)与VARCHAR(4)存储区别

<table> 
 <tbody> 
 <tr> 
 <td> 插入值 </td> 
 <td> CHAR(4)&nbsp; </td> 
 <td> 存储需求 </td> 
 <td> &nbsp;VARCHAR(4)&nbsp; </td> 
 <td> 存储需求 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; &nbsp;'' </td> 
 <td> '&nbsp; &nbsp; ' </td> 
 <td> 4个字节 </td> 
 <td> ' ' </td> 
 <td> &nbsp;1字节 </td> 
 </tr> 
 <tr> 
 <td> ‘ab’ </td> 
 <td> ‘ab&nbsp;&nbsp;’ </td> 
 <td> 4字节&nbsp; </td> 
 <td> ‘ab’ </td> 
 <td> 3字节 </td> 
 </tr> 
 <tr> 
 <td> ‘abc’ </td> 
 <td> ‘abc’ </td> 
 <td> 4字节&nbsp; </td> 
 <td> ‘abc’ </td> 
 <td> 4字节 </td> 
 </tr> 
 <tr> 
 <td> ‘abcd’ </td> 
 <td> ‘abcd’ </td> 
 <td> 4字节&nbsp; </td> 
 <td> ‘abcd’ </td> 
 <td> 5字节 </td> 
 </tr> 
 <tr> 
 <td> ‘abcdef’ </td> 
 <td> ‘abcd’ </td> 
 <td> 4字节&nbsp; </td> 
 <td> ‘abcd’ </td> 
 <td> 5字节 </td> 
 </tr> 
 </tbody> 
</table>

对比结果可以看到，CHAR(4) 定义了固定长度为4的列，不管存入的数据长度为多少，所占用的空间均为4个字节。VARCHAR(4) 定义的列所占的字节数为实际长度加1。

当查询时CHAR(4) 和VARCHAR(4) 的值并不一定相同，如下面这个例子。

**二 创建tmp8表，定义字段ch和vch数据类型依次为CHAR(4)、VARCHAR(4)向表中插入数据"ab"**

创建表tmp8：

CREATE TABLE tmp8(
    ch  CHAR(4),  vch  VARCHAR(4)
    );

输入数据：

mysql> INSERT INTO tmp8 VALUES('ab  ', 'ab  ');
    Query OK, 1 row affected (0.04 sec)

查询结果：

mysql> SELECT concat('(', ch, ')'), concat('(',vch,')') FROM tmp8;
    +----------------------+---------------------+
    | concat('(', ch, ')') | concat('(',vch,')') |
    +----------------------+---------------------+
    | (ab)                 | (ab  )              |
    +----------------------+---------------------+
    1 row in set (0.02 sec)

从查询结果可以看到，ch在保存“ab  ”时将末尾的两个空格删除了，而vch字段保留了末尾的两个空格。

**三 创建表tmp9，定义ENUM类型的列enm('first'，'second'，'third')，查看列成员的索引值**

首先，创建tmp9表：

mysql> CREATE TABLE tmp9( enm ENUM('first','second','third') );
    Query OK, 0 rows affected (0.17 sec)

插入各个列值：

mysql> INSERT INTO tmp9 values('first'),('second') ,('third') , (NULL);
    Query OK, 4 rows affected (0.03 sec)
    Records: 4  Duplicates: 0  Warnings: 0

查看索引值：

mysql> SELECT enm, enm+0 FROM tmp9;
    +--------+-------+
    | enm    | enm+0 |
    +--------+-------+
    | first  |     1 |
    | second |     2 |
    | third  |     3 |
    | NULL   |  NULL |
    +--------+-------+
    4 rows in set (0.01 sec)

可以看到，这里的索引值和前面所述的相同。

ENUM列总有一个默认值。如果将ENUM列声明为NULL，NULL值则为该列的一个有效值，并且默认值为NULL。如果ENUM列被声明为NOT NULL，其默认值为允许的值列表的第1个元素。

**四 创建表tmp10，定义INT类型的soc字段，ENUM类型的字段level，列表值为('excellent','good', 'bad')，向表tmp10中插入数据’good’，1，2，3，'best'**

首先，创建数据表：

mysql> CREATE TABLE tmp10 (soc INT, level enum('excellent', 'good','bad') );
    Query OK, 0 rows affected (0.16 sec)

插入数据：

mysql> INSERT INTO tmp10 values(70,'good'), (90,1),(75,2),(50,3);
    Query OK, 4 rows affected (0.03 sec)
    Records: 4  Duplicates: 0  Warnings: 0

再次插入数据：

mysql> INSERT INTO tmp10 values (100,'best');
    ERROR 1265 (01000): Data truncated for column 'level' at row 1

这里系统提示错误信息，可以看到，由于字符串值“best”不在ENUM列表中，所以对数据进行了阻止插入操作，查询结果如下：

mysql> SELECT * FROM tmp10;
    +------+-----------+
    | soc  | level     |
    +------+-----------+
    |   70 | good      |
    |   90 | excellent |
    |   75 | good      |
    |   50 | bad       |
    +------+-----------+
    4 rows in set (0.00 sec)

由结果可以看到，因为ENUM列表中的值在MySQL中都是以编号序列存储的，因此，插入列表中的值“good”或者插入其对应序号'2'的结果是相同的；"best"不是列表中的值，因此不能插入数据。

**五 创建表tmp11，定义SET类型的字段s，取值列表为('a', 'b', 'c', 'd')，插入数据('a')，('a,b,a')，('c,a,d')，('a,x,b,y')**

首先创建表tmp11：

mysql> CREATE TABLE tmp11 ( s SET('a', 'b', 'c', 'd'));
    Query OK, 0 rows affected (0.17 sec)

插入数据：

mysql> INSERT INTO tmp11 values('a'),( 'a,b,a'),('c,a,d');
    Query OK, 3 rows affected (0.03 sec)
    Records: 3  Duplicates: 0  Warnings: 0

再次插入数据：

mysql> INSERT INTO tmp11 values ('a,x,b,y');
    ERROR 1265 (01000): Data truncated for column 's' at row 1

由于插入了SET列不支持的值，因此MySQL给出错误提示。

查看结果：

mysql> SELECT * FROM tmp11;
    +-------+
    | s     |
    +-------+
    | a     |
    | a,b   |
    | a,c,d |
    +-------+
    3 rows in set (0.00 sec)

从结果可以看到，对于SET来说如果插入的值为重复的，则只取一个，例如“a,b,a”，则结果为“a,b”；如果插入了不按顺序排列值，则自动按顺序插入，例如“c,a,d”，结果为“a,c,d”；如果插入了不正确值，该值将被阻止插入，例如插入值“a,x,b,y”。

**六 创建表tmp12，定义BIT(4)类型的字段b，向表中插入数据2、9、15、16**

首先创建表tmp12，SQL语句如下：

mysql> CREATE TABLE tmp12( b BIT(4) );
    Query OK, 0 rows affected (0.17 sec)

插入数据：

mysql> INSERT INTO tmp12 VALUES(2), (9), (15);
    Query OK, 3 rows affected (0.03 sec)
    Records: 3  Duplicates: 0  Warnings: 0

查询插入结果：

mysql> SELECT BIN(b+0) FROM tmp12;
    +----------+
    | BIN(b+0) |
    +----------+
    | 10       |
    | 1001     |
    | 1111     |
    +----------+
    3 rows in set (0.00 sec)

b+0表示将二进制的结果转换为对应的数字的值，BIN() 函数将数字转换为二进制。从结果可以看到，成功的将3个数插入到表中。

**七 创建表tmp13，定义BINARY(3)类型的字段b和VARBINARY(3)类型的字段vb，并向表中插入数据'5'，比较两个字段的存储空间**

首先创建表tmp13，输入SQL语句如下：

CREATE TABLE tmp13(
    b binary(3),  vb varbinary(30)
    );

插入数据：

mysql> INSERT INTO tmp13 VALUES(5,5);
    Query OK, 1 row affected (0.02 sec)

查看两个字段存储数据的长度：

mysql> SELECT length(b), length(vb) FROM tmp13;
    +-----------+------------+
    | length(b) | length(vb) |
    +-----------+------------+
    |         3 |          1 |
    +-----------+------------+
    1 row in set (0.00 sec)

可以看到，b字段的值数据长度为3，而vb字段的数据长度仅为插入的一个字符的长度1。

如果想要进一步确认'5'在两个字段中不同的存储方式，输入如下语句：

mysql> SELECT b,vb,b = '5', b='5\0\0',vb='5',vb = '5\0\0' FROM tmp13;
    +------+------+---------+-----------+--------+--------------+
    | b    | vb   | b = '5' | b='5\0\0' | vb='5' | vb = '5\0\0' |
    +------+------+---------+-----------+--------+--------------+
    | 5    | 5    |       0 |         1 |      1 |            0 |
    +------+------+---------+-----------+--------+--------------+
    1 row in set (0.00 sec)

由执行结果可以看出，b字段和vb字段的长度是截然不同的，因为b字段不足的空间填充了'\\0'，而vb字段则没有填充。

**八 字符类型**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70]: /images/20210724/2f250ec376004225bf579a776928d4d1.png