字符串类型：

偏执的太偏执、 2022-01-13 20:23 173阅读 0赞

###     ###

mysql> \#字符串类型

mysql> \#char varchar text blob

mysql> \#最容易用到char和varchar的区别

mysql>

mysql> \#char 定长字符串，

mysql> \#varchar 变长类型

mysql> \#如姓名 char(6)

mysql> \#1 张三

mysql> \#2 李四

mysql> \#3 王五

mysql> \#4 李连杰

mysql> \#5 阿拉木汗

mysql> \#

mysql> \#在查找行记录时，都是定长，可以通过行数与行的长度计算出文件指针的偏移量

mysql> \#定长，不论够不够指定的长度，实际都占据

mysql> \#如果不够指定长度，用空格在末尾补齐到指定长度

mysql> \#

mysql> \#对于varchar变长，不用空格补齐，在列内容前有一到两个字节，标识列的长度

mysql> \#char 宽度M 可存M 实际I 实占空间M 利用率100%

mysql> \#varchar宽度M 可存M 实际I 实占I+(1-2)字节 利用率小于100%

mysql> create table test(

\-> ca char(6) not null default '',

\-> vca varchar(6) not null default ''

\-> )engine myisam charset utf8;

Query OK, 0 rows affected (0.01 sec)

mysql> desc

\-> test;

\+-------+------------+------+-----+---------+-------+

\+-------+------------+------+-----+---------+-------+

| ca | char(6) | NO | | | |

| vca | varchar(6) | NO | | | |

\+-------+------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

mysql> select \* from test;

Empty set (0.00 sec)

mysql> insert into test values('hello','hell0');

Query OK, 1 row affected (0.00 sec)

mysql> select \* from test;

\+-------+-------+

| ca | vca |

\+-------+-------+

| hello | hell0 |

\+-------+-------+

1 row in set (0.00 sec)

mysql> insert into test values('aa ','aa ');

Query OK, 1 row affected (0.00 sec)

mysql> select \* from test;

\+-------+-------+

| ca | vca |

\+-------+-------+

| hello | hell0 |

| aa | aa |

\+-------+-------+

2 rows in set (0.00 sec)

mysql> \#concat 函数 连接字符串

mysql> select concat(ca,'!'),concat(vca,'!') from test;

\+----------------+-----------------+

| concat(ca,'!') | concat(vca,'!') |

\+----------------+-----------------+

| hello! | hell0! |

| aa! | aa ! |

\+----------------+-----------------+

2 rows in set (0.03 sec)

**mysql> \#注意：char类型：存储时如果不够指定长度，内部用空格补齐**

**mysql> \#在取出时，再把右侧的空格删掉；**

**mysql> \#这就意味着，如果右侧有空格，将会丢失；**

**mysql> \#区别：**

**mysql> \#长度：**

**mysql> \#取出长度：**

**mysql> \#速度上：定长速度快；**

**mysql> \#char长度0-255 varchar长度0-65535；**

**mysql> \#**

**mysql> \#char(M)和varchar(M)限制的是字符，不是字节**

**mysql> \#char(2) charset utf8,能存2 个utf8字符，比如中国**

**mysql>**

**mysql> \#cahr(M) varchar(M)都是限定的字符数，不是字节数**

**mysql> \#一个utf8编码字符，由3个字节**

mysql> insert into test values('中国人','华夏民族源头');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select \* from test;

\+--------+--------+

| ca | vca |

\+--------+--------+

| hello | hell0 |

| aa | aa |

| 中国人 | 华夏民 |

\+--------+--------+

3 rows in set (0.00 sec)

mysql> insert into test values('hello world','pretty woman');

Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select \* from test;

\+--------+--------+

| ca | vca |

\+--------+--------+

| hello | hell0 |

| aa | aa |

| 中国人 | 华夏民 |

| hello | pretty |

\+--------+--------+

4 rows in set (0.00 sec)

mysql> set names utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values('中国人','华夏民族源头');

Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select \* from test;

\+--------------+--------------+

| ca | vca |

\+--------------+--------------+

| hello | hell0 |

| aa | aa |

| 脰脨鹿煤脠脣 | 禄陋脧脛脙帽 |

| hello | pretty |

| | |

\+--------------+--------------+

5 rows in set (0.00 sec)

mysql> set names gbk;

Query OK, 0 rows affected (0.00 sec)

mysql> select \* from test;

\+---------+--------+

| ca | vca |

\+---------+--------+

| hello | hell0 |

| aa | aa |

| ???ú?? | ?????? |

| hello | pretty |

| | |

\+---------+--------+

5 rows in set (0.02 sec)

mysql> set names utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> select \* from test;

\+--------------+--------------+

| ca | vca |

\+--------------+--------------+

| hello | hell0 |

| aa | aa |

| 脰脨鹿煤脠脣 | 禄陋脧脛脙帽 |

| hello | pretty |

| | |

\+--------------+--------------+

5 rows in set (0.00 sec)

mysql> \#text 长度 65536存储文章

mysql> \#注意：text没必要加默认值

mysql> \#test 文本类型，一般用来存储文章内容，新闻内容

mysql> \#声明text列，不必默认值

mysql> create table test2(

\-> artitle text not null default ''

\-> );

Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql>

mysql> desc test2;

\+---------+------+------+-----+---------+-------+

\+---------+------+------+-----+---------+-------+

\+---------+------+------+-----+---------+-------+

1 row in set (0.01 sec)

mysql> alter table test2 add img blob;

Query OK, 0 rows affected (0.08 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test2;

\+---------+------+------+-----+---------+-------+

\+---------+------+------+-----+---------+-------+

| img | blob | YES | | NULL | |

\+---------+------+------+-----+---------+-------+

2 rows in set (0.01 sec)

mysql> \#blob 是二进制类型，用来存储图像音频等二进制信息；

mysql> insert into test2 values('qingqing','zhangfei ');

Query OK, 1 row affected (0.00 sec)

mysql> select \* from test2;

\+----------+-----------+

| artitle | img |

\+----------+-----------+

| qingqing | zhangfei |

\+----------+-----------+

1 row in set (0.00 sec)

mysql> \#意义在于：二进制从0-255每个字节都有可能出现，

mysql> \#blob类型在于防止因为字符集问题，导致信息丢失

mysql> \#如一张图片里有0xFF字节，这个在ascii字符集中认为非法，被过滤

mysql> \#下次取的时候，图片因此被损坏；

mysql> \#exit;

mysql> exit;

转载于:https://blog.51cto.com/duguodong/1386394

字符串类型：

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 python字符串类型判断(python中datatype()测试字符串类型)

相关 Mysql字符串类型

相关数据类型-字符串

相关 bilibiliC++13数据类型-字符串类型

相关 mysql 转字符串类型

相关 Java字符串类型

相关【基础】字符串类型

相关 mysql数据类型之字符串类型

相关 Redis基本数据类型—字符串类型

相关字符串类型：