【MySQL基础】03：MySQL数据类型（里有避坑指南）

最新推荐文章于 2023-03-27 21:47:16 发布
最新推荐文章于 2023-03-27 21:47:16 发布
阅读量315 收藏
点赞数
分类专栏： MySQL 文章标签： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zyplanke/article/details/102934027
版权

 

     本教程以MySQL8为主版本（内容兼顾MySQL5.7）。

     所有MySQL文章的目录为：总目录    https://blog.csdn.net/zyplanke/article/details/102968014     

    参照官方文档，梳理总结了常用的MySQL的数据类型。包括字符类型、数值类型、日间时间类型。

一个大坑：在where条件中字符和数字可以直接用等号，且结果正确。比如字段ColA类型为字符串，标准SQL写法：where ColA ='123'，非标准写法为where ColA =123，也能获得正确的结果。 但是可能引起严重的性能问题和锁等待问题，因为在MySQL内部执行计划是不同的：标准SQL写法有索引则会使用索引直接获得结果；但非标准写法，无法匹配使用索引，先全表扫描然后从结果集中最后过滤筛选（最后筛选时才匹配上）。 同因此原因，在InnoDB下，非标准写法因全表扫描导致全部行都锁定，效果等同于表锁,可能引发严重的锁冲突和等待。

 

字符类型

Type定义注意
CHAR(N)可存放固定N个字符, N最大值为255值不够长时，自动右补空格存储。
值超长，自动去除尾部字符。
VARCHAR(N)可存放最多N个字符, N最大值为65535　
BINARY二进制字符串　
VARBINARY 二进制字符串　
BLOB二进制大对象，可以容纳可变数量的数据一般存放图片，音频等二进制数据
TEXT字符类大对象一般存放文本等字符数据
ENUM（单选）枚举类型。　
SET（多选）集合枚举类型　
  • 关于字符串长度单位是字节还是字符的说明：

 在字符集为utf8的情况下（对于MySQL5.7，需要将MySQL server端和connect等字符集设置为utf8mb4），字符串类型长度定义的单位是字符，不是字节。 length函数计算的是字节数，char_length函数计算的是字符数。验证如下：

mysql> create table TB (colA char(13), colB varchar(13));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into TB values ('中华人民共和国地图abc中国', '中华人民共和国地图abc中国');
ERROR 1406 (22001): Data too long for column 'colA' at row 1

mysql> insert into TB values ('中华人民共和国地图abc中', '中华人民共和国地图abc中国');  
ERROR 1406 (22001): Data too long for column 'colB' at row 1

mysql> insert into TB values ('中华人民共和国地图abc中', '中华人民共和国地图abc中');
Query OK, 1 row affected (0.00 sec)

mysql>  select length(colA), char_length(colA), length(colB), char_length(colB) from TB;      
+--------------+-------------------+--------------+-------------------+
| length(colA) | char_length(colA) | length(colB) | char_length(colB) |
+--------------+-------------------+--------------+-------------------+
|           33 |                13 |           33 |                13 |
+--------------+-------------------+--------------+-------------------+
1 row in set (0.00 sec)
  • 关于字段尾部空格的处理

对于CHAR类型，MySQL在检索时会自动把值的尾部空格删除：

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.01 sec)

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

mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;
+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (ab  )              | (ab)                |
+---------------------+---------------------+
1 row in set (0.06 sec)

mysql> SELECT LENGTH(v), LENGTH(c)  FROM vc;
+-----------+-----------+
| LENGTH(v) | LENGTH(c) |
+-----------+-----------+
|         4 |         2 |
+-----------+-----------+
1 row in set (0.00 sec)

   因为在MySQL中所有字符类型都是PAD SPACE类型。这意味着 在所有 CHAR，VARCHAR和 TEXT类型的值进行比较时，都会按统一的PAD处理，可以不考虑尾部空格情况。 但是Like判断除外：

mysql>  CREATE TABLE names (myname CHAR(10));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO names VALUES ('Jones');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT myname = 'Jones', myname = 'Jones  ' FROM names;
+------------------+--------------------+
| myname = 'Jones' | myname = 'Jones  ' |
+------------------+--------------------+
|                1 |                  1 |
+------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT myname LIKE 'Jones', myname LIKE 'Jones  ' FROM names;
+---------------------+-----------------------+
| myname LIKE 'Jones' | myname LIKE 'Jones  ' |
+---------------------+-----------------------+
|                   1 |                     0 |
+---------------------+-----------------------+
1 row in set (0.00 sec)

数值类型

分类Type字节值范围备注
整数TINYINT1有符号数：-128 to 127
无符号数：0 to 255		默认是有符号
SMALLINT2有符号数：-32768 to 32767
无符号数：0 to 65535		默认是有符号
MEDIUMINT3有符号数：-8388608 to 8388607
无符号数：0 to 16777215		默认是有符号
INT、INTEGER4有符号数：-2147483648 to 2147483647
无符号数：0 to 4294967295		默认是有符号
BIGINT8有符号数：-9223372036854775808 to 9223372036854775807
无符号数：0 to 18446744073709551615		默认是有符号
浮点FLOAT4 -3.402823466E+38 to -1.175494351E-38, 0 以及
1.175494351E-38 to 3.402823466E+38		　
DOUBLE8 -1.7976931348623157E+308 to -2.2250738585072014E-308, 以及2.2250738585072014E-308 to 1.7976931348623157E+308　
DEC(M,D)、DECIMAL(M,D)M+2M是数字总位数（包括小数位），D是小数位数　
位BIT(M)1~8最小值：BIT(1)，最大值：BIT(64)　

(从MySQL 8.0.17起，zerofill已经废弃)。对于整数类型，还可以与zerofill一起指定显示宽度（display width），不影响实际存储。在type名词后用小括号设置显示宽度（display width）。zerofill就是用“0”填充，就是在数字位数不够时，显示时自动在前面用“0”填满至指定宽度。例如：

mysql> create table tb1( col1 int , col2 int(6), col3 int(6) zerofill );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tb1 values ( 11, 11, 11); 
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;
+------+------+--------+
| col1 | col2 | col3   |
+------+------+--------+
|   11 |   11 | 000011 |
+------+------+--------+
1 row in set (0.00 sec)

mysql> insert into tb1 values ( 222222222, 222222222, 222222222);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;
+-----------+-----------+-----------+
| col1      | col2      | col3      |
+-----------+-----------+-----------+
|        11 |        11 |    000011 |
| 222222222 | 222222222 | 222222222 |
+-----------+-----------+-----------+
2 rows in set (0.00 sec)

 

日间时间类型

Type定义注意
DATE存放年月日没有时间。范围'1000-01-01' to '9999-12-31'。　
DATETIME存放年月日以及时间。范围'1000-01-01 00:00:00' to '9999-12-31 23:59:59'。精度：六位微妙　
TIMESTAMP存放年月日以及时间。范围UTC'1970-01-01 00:00:01' to '2038-01-19 03:14:07'。精度：六位微妙　
TIME时分秒。范围 '-838:59:59'到 '838:59:59'。小时范围很大，因为该类型仅可以用来表示一天中的某个时间，还可表示两个事件之间的时间间隔　
YEARYEAR类型是1字节类型，用于表示年份值。范围 1901为2155，和 0000　

为日期时间类型的字段自动赋值

         对于TIMESTAMP或DATETIME类型的字段，有两个自动赋值的属性：DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP。 

  • DEFAULT CURRENT_TIMESTAMP 在insert新增一条记录时，如果insert中没有赋值，则自动将当前时间作为该字段的值。一般用于存放数据创建时间(create_time)。
  • ON UPDATE CURRENT_TIMESTAMP 在update更新一条记录时，当该数据的其他字段发生变化时，此字段值也自动更新为最新的当前时间，一般用于存放数据的更改时间(change_time)。

测试验证如下：

mysql> CREATE TABLE tt (
       colA DATETIME,
       colB DATETIME DEFAULT CURRENT_TIMESTAMP,
       colC DATETIME ON UPDATE CURRENT_TIMESTAMP,
       colD DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tt values ('2001-01-01 01:01:01',  '2001-01-01 01:01:01', '2001-01-01 01:01:01', '2001-01-01 01:01:01');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
+---------------------+---------------------+---------------------+---------------------+
| colA                | colB                | colC                | colD                |
+---------------------+---------------------+---------------------+---------------------+
| 2001-01-01 01:01:01 | 2001-01-01 01:01:01 | 2001-01-01 01:01:01 | 2001-01-01 01:01:01 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into tt (colA) values ('2002-02-02 02:02:02');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
+---------------------+---------------------+---------------------+---------------------+
| colA                | colB                | colC                | colD                |
+---------------------+---------------------+---------------------+---------------------+
| 2001-01-01 01:01:01 | 2001-01-01 01:01:01 | 2001-01-01 01:01:01 | 2001-01-01 01:01:01 |
| 2002-02-02 02:02:02 | 2019-11-06 22:40:08 | NULL                | 2019-11-06 22:40:08 |
+---------------------+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> update tt set colA='2003-03-03 03:03:03' where colA='2002-02-02 02:02:02';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tt;
+---------------------+---------------------+---------------------+---------------------+
| colA                | colB                | colC                | colD                |
+---------------------+---------------------+---------------------+---------------------+
| 2001-01-01 01:01:01 | 2001-01-01 01:01:01 | 2001-01-01 01:01:01 | 2001-01-01 01:01:01 |
| 2003-03-03 03:03:03 | 2019-11-06 22:40:08 | 2019-11-06 22:41:44 | 2019-11-06 22:41:44 |
+---------------------+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)

JSON类型

    包含若干个元素的JSON数组，用逗号分隔，两边中括号。格式为：

["abc", 10, null, true, false]

   包含Key:Value形式的多个元素，用逗号分隔，两边花括号。格式为：

{"k1": "value", "k2": 10}

   嵌套形式：

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

   以上是基础知识。 下面演示JSON在MySQLMySQL中的用法：

mysql> CREATE TABLE t1 (jdoc JSON);
ERROR 1050 (42S01): Table 't1' already exists
mysql> CREATE TABLE tt (jdoc JSON); 
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tt VALUES('{"key1": "value1", "key2": "value2"}'); 
Query OK, 1 row affected (0.00 sec)

#插入一个不满足JSON格式要求的数据
mysql> INSERT INTO t1 VALUES('[1, 2,');
ERROR 1366 (HY000): Incorrect integer value: '[1, 2,' for column 'i' at row 1

   还有很多JSON相关操作，见官方文档：https://dev.mysql.com/doc/refman/8.0/en/json.html

Spatial空间数据类型

     需要先理解空间涉及的概念。见官方文档https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html

zyplanke
关注
专栏目录
第79讲： MySQL Binlog二进制日志恢复误删数据的实践指南
江晓龙的博客
12-18 1万+
根据需求我们得知要恢复误删除的这张表，那么在事件信息中找到创建该表的事件，这个事件的开始标识位就作为我们截取Binlog日志的开始标识位号，结束标识位更好确定了，在事件信息中找到删除表的事件，这个事件的开始标识位也就作为我们截取Binlog日志的结束标识位，开始标识位号是上一个事件的结束表示为号。注意：一定不要将删除表事件的结束标识位号作为截取Binlog日志的结束标识位号，如果截取Binlog日志的结束标识位号是删除表的结束标识位号，就会把删除表的操作在还原时又进行了一遍，数据就不能恢复了。
hive中bigint和varchar字段做关联，关联数据错误的解决方法
yyf960126的博客
09-07 6688
把bigint和varchar都隐式转换成String类型的就可以关联出正确的结果了 cast(t1.PARTY_NUM as string)=cast(t12.customerId as string)
presto/trino连接clickhouse 字符串问题
qq_39158142的博客
06-17 2532
当使用presto将clickhouse作为数据源进行查数据，如果其中有字符类型的话，比如说String或者FixString时，当我们对该字段进行筛选时，会报错 varbinary
遇到的问题及解决方法
clover_oreo的博客
07-11 4267
如图所示，在编写判断当前团队程序员数量时，判断语句 !(team[i] instanceof Architect) 内部报错，提示“Inconvertible types; cannot cast 'Project.team.domain.Programmer' to 'Project.team.domain.Architect'”，翻译过来就是 “不可转换类型；无法将“Project.team.domain.Programme
ERROR: operator does not exist: character = integer
不忘初心的博客
07-28 8881
错误    ERROR:  operator does not exist: character = integer    原因：PostgreSQL8.3以后，取消了默认类型转换。因此需要使比较的类型保持一致。可以看cast函数。 因此需要自己转化数据库字符串类型varchar(255)为int4，但是直接在数据库上操作会报错， 只要执行一句：
java小白零基础（七）
A17719032175的博客
03-27 272
/抄别人的异常类 单身人士的异常类 class SinglerException extends Exception {super();buy(false);} //true 是单身 false 不是单身 public static void buy(boolean isSingle) throws Exception {throw new SinglerException("单身不能进店购买");} System . out . println("情侣买一送一！！！");} }
MySQL内幕揭秘：探索MySQL调优指南，解锁MySQL的强大功能
05-25
从最初的beta版本到现在的成熟产品，MySQL经历了多次重大更新，添加了诸多功能，如子查询、视图、存储过程、全文搜索、JSON数据类型、窗口函数等。它在各个大型平台和企业中扮演着关键角色，如谷歌、Facebook、...
MySQL错误日志：数据库故障诊断的指南针
最新发布
07-22
### MySQL 错误日志：数据库故障诊断的指南针 #### MySQL 错误日志的重要性 MySQL 错误日志是数据库管理员（DBA）在日常运维工作中不可或缺的工具之一。它不仅记录了数据库在运行过程中遇到的各种错误信息，还为...
MySQL分区表：万字详解与实践指南
05-28
MySQL支持多种分区类型，每种类型都有其特定的应用场景： 1. **范围分区（RANGE）** - **定义**：范围分区根据某个列的值落在给定的连续区间内来进行数据分区。 - **示例**：按照日期的年份进行分区。 ```sql ...
MySQL数据仓库指南.pdf
05-30
MySQL数据仓库指南是一份关于如何设计和开发MySQL数据仓库的详细指南。该指南不直接关注MySQL数据库本身的使用，而是重点关注数据仓库的构建与应用，旨在帮助读者建立起在多种商业场景中都能发挥作用的数据仓库系统...
关于hive元数据的错误
01-07
Exception in thread “main” java.lang.RuntimeException: org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.hdfs.server.namenode.SafeModeException): Cannot create directory /tmp/hive/root/92f8b98e-beeb-4136-a66b-f2df5d922784. Name node is in safe mode. The reported blocks 2381 needs additional 2
MySQL必知必会常识技巧实战宝典
08-16
课程目录： 01 数字类型：避免自增踩坑.mp4 02 字符串类型：不能忽略的COLLATION.mp4 03 日期类型：TIMESTAMP可能是巨坑.mp4 04非结构存储：用好JSON这张牌.mp4 05 表结构设计：忘记范式准则.mp4 06 表压缩：不仅仅是空间压缩.mp4 07 表的访问设计：你该选择SQL还是NoSQL?.mp4 08 索引：排序的艺术.mp4 09 索引组织表：万物皆索引.mp4 10 组合索引：用好，性能提升10倍!.mp4 11 索引出错：请理解CBO的工作原理.mp4 12 JOIN连接：到底能不能写JOIN?.mp4 13 子查询：放心地使用子查询功能吧!.mp4 14 分区表：哪些场景我不建议用分区表?.mp4 15 MySQL复制：最简单也最容易配置出错.mp4 16 读写分离设计：复制延迟?其实是你用错了.mp4 17 高可用设计：你怎么活用三大架构方案?.mp4 18 金融级高可用架构：必不可少的数据核对.mp4 19 高可用套件：选择这么多，你该如何选?.mp4 20 InnoDB Cluter:改变历史
PostgreSQL ERROR: operator does not exist: bigint = character varying
丘处机的专栏
03-16 1万+
类型时，PostgreSQL将使用这个cast来执行转换。如果没有定义该cast，PostgreSQL将无法执行该转换并抛出类似于“operator does not exist”这样的错误。是用于创建新的强制类型转换的SQL命令。该命令指定一个源类型和目标类型，并指定将源类型转换为目标类型时要使用的函数。在此情况下，创建了一个将。类型的列进行了比较，而 PostgreSQL 并没有提供这两个类型之间的默认比较操作符。参数表示当需要自动转换时，该cast将被隐式地使用。类型的cast，其中。
cannot be applied to
热门推荐
expect521的博客
09-11 4万+
错误示例：代码块public class Test { public static void main(String[] args) { test(111); } public static void test(String str) { System.out.println(str); } } 错误分析test(String str)
Change type of varchar field to integer: “cannot be cast automatically to type integer”
会飞的海象
12-17 708
ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (col_name::integer); ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (trim(col_name)::integer);
cannot apply unary operator `-` to type `u8`
阿昊的博客
01-14 688
cannot apply unary operator `-` to type `u8`error[E0600]: cannot apply unary operator `-` to type `u8` --> src\main.rs:17:19 | 17 | let u8_1:u8 = -1; | ^^ | | | cannot apply una...
Hive报错处理方法汇总
maclaren001的专栏
05-10 1263
1.报错信息： The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. at sun.reflect.NativeConstructorAccessorImpl.newInstan
MySQL基础：数据类型与操作指南
在MySQL中，数据类型用于定义表结构中各列的数据格式和范围。常见的数据类型包括： - **INT**: 存储整数，占用4字节，可存储-2^31到2^31-1的整数。 - **FLOAT**: 单精度浮点数，占用4字节，用于存储带有小数的数值。...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值