-
4.0版本及以下,MySQL中varchar长度是按
字节
展示,如varchar(20),指的是20字节
; -
5.0版本及以上,MySQL中varchar长度是按
字符
展示。如varchar(20),指的是20字符
。
当然,行
总长度还是65535字节,而字符和字节的换算,则与编码方式有关,不同的字符所占的字节是不同的。编码划分如下:
GBK编码:
一个英文字符占一个字节,中文2字节,单字符最大可占用2个字节。
UTF-8编码:
一个英文字符占一个字节,中文3字节,单字符最大可占用3个字节。
utf8mb4编码:
一个英文字符占一个字节,中文3字节,单字符最大占4个字节(如emoji表情4字节)。
假设当前还有6字节可以存放字符,按单字符占用最大字节数来算,可以存放3个GBK、或2个utf8、或1个utf8mb4。
4、思考:既然VARCHAR长度可变,那我要不要定到最大?
没错,相信你已经有答案了,别这么干!
就像使用VARCHAR(5)和VARCHAR(200)存储 '陈哈哈'的磁盘空间开销是一样的。那么使用更短的列有什么优势呢?
事实证明有很大的优势。更长的列会消耗更多的内存,因为MySQL通常会分配固定大小的内存块来保存内部值。
当然,在没拿到存储引擎存储的数据之前,并不会知道我这一行拿出来的数据到底有多长,可能长度只有1,可能长度是500,那怎么办呢?那就只能先把最大空间分配好了,避免放不下的问题发生,这样实际上对于真实数据较短的varchar确实会造成空间的浪费。
举例:我向数据类型为:varchar(1000)的列插入了1024行数据,但是每个只存一个字符,那么这1024行真实数据量其实只有1K,但是我却需要约1M的内存去适应他。所以最好的策略是只分配真正需要的空间。
5、在SQL中需要注意的点
下面通过一个具体的示例来说明CHAR和VARCHAR类型存储时的区别。我们创建一张同时存在CHAR(10)
字段、VARCHAR(10)
字段的表,并且往里面插入一些值来做对比验证:
– 建表语句
CREATE TABLE str_table
(
id
int(11) NOT NULL AUTO_INCREMENT,
str_char
char(10) DEFAULT NULL,
str_varchar
varchar(10) DEFAULT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
分别插入一些字符串前面和后面都有空格的示例
– 插入测试数据
INSERT INTO str_table
(id
, str_char
, str_varchar
)
VALUES
(null, ‘陈哈哈’, ‘陈哈哈’),
(null, ’ 陈哈哈’, ’ 陈哈哈’),
(null, '陈哈哈 ', '陈哈哈 ');
测试数据查询语句如下,通过拼接能更好的看出对比效果:
– 测试数据查询
select id,concat("|",str_char,"|") as char
,concat("|",str_varchar,"|") as varchar
from str_table;
mysql> select id,concat("|",str_char,"|") as char
,concat("|",str_varchar,"|") as varchar
from str_table;
±—±--------------±--------------+
| id
【一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义】
浏览器打开:qq.cn.hn/FTf 免费领取
| char | varchar |
±—±--------------±--------------+
| 6 | |陈哈哈| | |陈哈哈| |
| 7 | | 陈哈哈| | | 陈哈哈| |
| 8 | |陈哈哈| | |陈哈哈 | |
±—±--------------±--------------+
3 rows in set (0.00 sec)
-
当检索这些值的时候,会发现id=8行中,char类型的"陈哈哈 "末尾的空格被截断了,而VARCHAR(10)字段存储相同的值时,末尾的空格被保留了。
-
另外,id=7行的数据前面空格都被保留了。
可见,CHAR会默认切掉字符串末尾的空格,如果需要保留末尾的空格,记得用varchar类型!
追问2:varchar(50)、char(50)中50的涵义是什么?
varchar(50)
VARCHAR列中的值为可变长字符串。长度可以指定为0到65535之间的值。VARCHAR的最大有效长度由最大行大小和使用的字符集确定。
MySQL 4.1之前
:VARCHAR(50)的“50”指的是50字节(bytes)。如果存放UTF8汉字时,那么最多只能存放16个(每个汉字3字节)。
MySQL 4.1版本及以后
:VARCHAR(50)的“50”指的是50字符(character),无论存放的是数字、字母还是UTF8汉字(每个汉字3字节),都可以存放50个。
char(50)
CHAR和VARCHAR类型声明的长度表示保存的最大字符数。例如,CHAR(30)可以占用30个字符。
对于MyISAM表,推荐CHAR类型;对于InnoDB表,推荐VARCHAR类型。
另外,在进行检索的时候,若列值的尾部含有空格,则CHAR列会删除其尾部的空格,而VARCHAR则会保留空格。
追问3:那int(10)中10的涵义呢?int(1)和int(20)有什么不同?
int的范围
有符号的整型范围是-2147483648~2147483647 (-2^32 ~ 2^32)
无符号的整型范围是0~4294967295
int(10)的意思
int(10)的意思是假设有一个变量名为id,它的能显示的宽度能显示10位。在使用id时,假如我给id输入10,那么mysql会默认给你存储0000000010。
当你输入的数据不足10位时,会自动帮你补全位数。
假如我设计的id字段是int(20),那么我输入id = 10
时,mysql会自动补全18个0(00000000000000000010),补到20位为止。
引用一下《高性能MySQL》的说明:
课间休息,欣赏一下群里同学端午节爬山的美景。想到他在爬山时,我却在加班,我就……
坐标:华山。
作者:if you
============================================================================================
摘取一下SQL大腿群
里同学的回复吧:
例:ab两表关联,a表数据有,b表关联数据没有,a表的就不要显示或显示null的问题;几个连接怎么使用,总结一下:
左链接取A集合,右链接取B集合,full join取并集,inner join 取交集。
课间休息,欣赏一下群里另一位同学爬山的美景。又想到他在爬山时,我还在加班,我就艹了😐😐😐(*顺道测一下CSDN emoji兼容~ 邪恶~*🙃)!
坐标:???知道的同学在评论区写一下。
作者:🙉
============================================================================================
1、SQL语句中隐式转换的坑
先看一下官方的隐试转换说明:
翻译成人话:
-
两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 <=> 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换。
-
两个参数都是字符串,会按照字符串来比较,不做类型转换
-
两个参数都是整数,按照整数来比较,不做类型转换
-
十六进制的值和非数字做比较时,会被当做二进制串
-
有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp
-
有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较
-
所有其他情况下,两个参数都会被转换为浮点数再进行比较
。(这里所说的浮点数一般默认为double类型
)
可以看到,非前六种以外的类型转换都要转成浮点类型来处理,这意味着什么?意味着MySQL承认了隐式转换这个事儿,还表示不爱看官方文档的哥们儿出问题活该
~~
我们用一些具体示例来看一下,通过下述SQL可见,当1234没有引号也就是整数时,'1234abcd' = 1234 → true
,说明MySQL对'1234abcd'做了转型,转成了浮点类型
,结果是:1234abcd => 1234
0:false;1:true
mysql> SELECT ‘1234abcd’ = ‘1234’;
±--------------------+
| ‘1234abcd’ = ‘1234’ |
±--------------------+
| 0 |
±--------------------+
1 row in set (0.00 sec)
0:false;1:true
mysql> SELECT ‘1234abcd’ = 1234;
±------------------+
| ‘1234abcd’ = 1234 |
±------------------+
| 1 |
±------------------+
1 row in set, 1 warning (0.00 sec)
为啥1234abcd => 1234呢? 其实’1234’和’abcd’都会转成浮点数,即:1234+0=1234
,非数字类型的都被直接转成了 0
mysql> SELECT ‘1234’ + ‘abcd’;
±----------------+
| ‘1234’ + ‘abcd’ |
±----------------+
| 1234 |
±----------------+
1 row in set, 1 warning (0.00 sec)
你发现了什么?原来字符串涉及到 +
、=
、-
、/
等等运算符时都会进行隐式转型,也就是转成double
,那么字符串转double是怎么转的呢?
转成:‘1aaaa’ = 1
mysql> SELECT ‘1aaaa’ = 1;
±------------+
| ‘1aaaa’ = 1 |
±------------+
| 1 |
±------------+
1 row in set, 1 warning (0.00 sec)
转成:‘a1111’ = 0
mysql> SELECT ‘a1111’ = 1;
±------------+
| ‘a1111’ = 1 |
±------------+
| 0 |
±------------+
1 row in set, 1 warning (0.00 sec)
转成:0 + 0 =0
mysql> SELECT ‘aa’ + ‘aa’ = 1;
±----------------+
| ‘aa’ + ‘aa’ = 1 |
±----------------+
| 0 |
±----------------+
1 row in set, 2 warnings (0.00 sec)
转成:0 + 0 + 1 =1
mysql> SELECT ‘aa’ + ‘aa’ + ‘1’ = 1;
±----------------------+
| ‘aa’ + ‘aa’ + ‘1’ = 1 |
±----------------------+
| 1 |
±----------------------+
1 row in set, 2 warnings (0.00 sec)
可见,是以字符串从左向右取值的,且从非数字起后面的值都被转成 0
,如a11111
,第一位为a
,则整体转为 0
;1aaaa
第一位为1
,第二位为a
,从第二位往后转成0
,得a11111
→ 0
mysql> SELECT * from t_user where password
=1234;
±—±----------±---------+
| id | username | password |
±—±----------±---------+
| 2 | 侨布斯 | 1234 |
| 3 | 提莫 | 1234abcd |
±—±----------±---------+
2 rows in set, 1 warning (0.00 sec)
现在我们就明白为什么能匹配到提莫
了。因为在不同类型转换时"1234abcd"被转成了浮点类型,"abcd"转成浮点型后为0
,因此MySQL判为:“1234abcd” = ‘1234’ + 0 。
2、黑客同学喜欢用隐式转换进行SQL注入攻击
通过第一部分隐式转换的了解,我们可以预测一些简单SQL注入的方式:
mysql> SELECT * from t_user where username=‘陈哈哈’ and password
=0;
±—±----------±---------+
| id | username | password |
±—±----------±---------+
| 1 | 陈哈哈 | abcd1234 |
±—±----------±---------+
1 row in set, 1 warning (0.00 sec)