mysql 支持的数据类型

程序员文章站 2022-03-16 15:12:41

mysql支持的数据类型 [TOC] 数据类型官方文档数值类型数值类型表 | 类型 | 大小 | 范围(有符号) | 范围(无符号)unsigned约束 | 用途 | | | | | | | | TINYINT | 1字节 | ( 128,127) | (0,255) | 小整数值 | | SM ......

mysql支持的数据类型

数据类型官方文档

数值类型

mysql支持所有标准sql数值数据类型。
这些类型包括严格数值数据类型(integer、smallint、decimal和numeric)，以及近似数值数据类型(float、real和double precision)。
关键字int是integer的同义词，关键字dec是decimal的同义词。
mysql支持的整数类型有tinyint、mediumint和bigint。下面的表显示了需要的每个整数类型的存储和范围。
对于小数的表示，mysql分为两种方式：浮点数和定点数。浮点数包括float(单精度)和double(双精度),而定点数只有decimal一种，
在mysql中以字符串的形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。
bit数据类型保存位字段值，并且支持myisam、memory、innodb和bdb表。

数值类型表

类型	大小	范围(有符号)	范围(无符号)unsigned约束	用途
tinyint	1字节	(-128,127)	(0,255)	小整数值
smallint	2字节	(-32768,32767)	(0,65535)	大整数值
mediumint	3字节	(-8388608，8388607)	(0，16777215)	大整数值
int或integer	4字节	(-2147483648，2147483 647)	(0，4294967295)	大整数值
bigint	8字节	(-9233372036854775808，9223372036854 775807)	(0，18446744073709551 615)	极大整数值
float	4字节float(255,30)	(-3.402823466e+38，-1.175494351e-38)，0，(1.175 494351e-38，3.402823466351e+38)	0，(1.175494351e-38，3.402823466e+38)	单精度浮点数值
double	8字节double(255,30)	(-1.7976931348623157e+308，-2.225073858 5072014 e-308)，0，(2.2250738585072014e-308，1.7976931348623157e+308)	0，(2.225 073 858 507 201 4 e-308，1.797 693 134 862 315 7 e+308)	双精准浮点数值
decimal	对decimal(m,d) ，如果m>d，为m+2否则为d+2double(65,30)	依赖于m和d的值	依赖于m和d的值	x小数值

整型的每一种都分有无符号（unsigned）和有符号（signed）两种类型（float和double总是带符号的），
在默认情况下声明的整型变量都是有符号的类型（char有点特别），如果需声明无符号类型的话就需要在类型前加上unsigned。
无符号版本和有符号版本的区别就是无符号类型能保存2倍于有符号类型的正整数数据，比如16位系统中一个int能存储的数据的范围为-32768~32767，
而unsigned能存储的数据范围则是0~65535。由于在计算机中,整数是以补码形式存放的。根据最高位的不同，如果是1,有符号数的话就是负数；
如果是无符号数,则都解释为正数。另外，unsigned若省略后一个关键字，大多数编译器都会认为是unsigned int。
示列:

有符合的8位前面第一位是表示符号的,后边只能显示7位
整数 int: create table 表 (id1 int(4),id2 int(11));
- int默认是有符号的
- int 默认11位整数
- 它能表示的数字的范围不被宽度约束#它只能约束数字的显示宽度
整数 int: create table 表 (id1 int unsigned,id2 int); #unsigned 指定取除int符号
小数 float: create table 表 (f1 float(5,2),d1 double(5,2)); #float单精度浮点数值 #double双精度浮点数值 (5,2)整数,小数范围限制
小数 float: create table 表 (f1 float,d1 double); 默认值
小数 float: create table 表(d1 decimal,d2 decimal(25,20));#decimal 小数值默认与指定(25,20)

# 创建表一个是默认宽度的int，一个是指定宽度的int(5)
mysql> create table t1 (id1 int,id2 int(5));
query ok, 0 rows affected (0.02 sec)

# 像t1中插入数据1，1
mysql> insert into t1 values (1,1);
query ok, 1 row affected (0.01 sec)

# 可以看出结果上并没有异常
mysql> select * from t1;
+------+------+
| id1  | id2  |
+------+------+
|    1 |    1 |
+------+------+
row in set (0.00 sec)

# 那么当我们插入了比宽度更大的值，会不会发生报错呢？
mysql> insert into t1 values (111111,111111);
query ok, 1 row affected (0.00 sec)

# 答案是否定的，id2仍然显示了正确的数值，没有受到宽度限制的影响
mysql> select * from t1;
+------------+--------+
| id1        | id2    |
+------------+--------+
| 0000000001 |  00001 |
| 0000111111 | 111111 |
+------------+--------+
rows in set (0.00 sec)

# 修改id1字段 给字段添加一个unsigned表示无符号
mysql> alter table t1 modify id1 int unsigned;
query ok, 0 rows affected (0.01 sec)
records: 0  duplicates: 0  warnings: 0

mysql> desc t1;
+-------+------------------+------+-----+---------+-------+
| field | type             | null | key | default | extra |
+-------+------------------+------+-----+---------+-------+
| id1   | int(10) unsigned | yes  |     | null    |       |
| id2   | int(5)           | yes  |     | null    |       |
+-------+------------------+------+-----+---------+-------+
rows in set (0.01 sec)

# 当给id1添加的数据大于214748364时，可以顺利插入
mysql> insert into t1 values (2147483648,2147483647);
query ok, 1 row affected (0.00 sec)

# 当给id2添加的数据大于214748364时，会报错
mysql> insert into t1 values (2147483647,2147483648);
error 1264 (22003): out of range value for column 'id2' at row 1

int整数示例

日期类型

#表示时间值的日期和时间类型为datetime、date、timestamp、time和year。
#每个时间类型有一个有效值范围和一个"零"值，当指定不合法的mysql不能表示的值时使用"零"值。
#timestamp类型有专有的自动更新特性，将在后面描述。

日期类型表

类型	大小 (字节)	范围	格式	用途
date	3	1000-01-01/9999-12-31	yyyy-mm-dd	年月日
time	3	'-838:59:59'/'838:59:59'	hh:mm:ss	时分秒
year	1	1901/2155	yyyy	年份值
datetime	8	1000-01-01 00:00:00/9999-12-31 23:59:59	yyyy-mm-dd hh:mm:ss	年月日时分秒
timestamp	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	yyyymmdd hhmmss	混合日期和时间值，时间戳

日期类型
- year 年
- date 年月日
- time 时分秒
- now() 函数现在时间
- datetime、timestamp 年月日时分秒
- create table t9(y year,d date,dt datetime not null default current_timestamp on update current_timestamp ts timestamp);

mysql> create table t4 (d date,t time,dt datetime);
query ok, 0 rows affected (0.02 sec)

mysql> desc t4;
+-------+----------+------+-----+---------+-------+
| field | type     | null | key | default | extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | yes  |     | null    |       |
| t     | time     | yes  |     | null    |       |
| dt    | datetime | yes  |     | null    |       |
+-------+----------+------+-----+---------+-------+
rows in set (0.01 sec)

mysql> insert into t4 values (now(),now(),now());
query ok, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t4;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
+------------+----------+---------------------+
row in set (0.00 sec)

mysql> insert into t4 values (null,null,null);
query ok, 1 row affected (0.01 sec)

mysql> select * from t4;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
| null       | null     | null                |
+------------+----------+---------------------+
rows in set (0.00 sec)

date/time/datetime示例
##################################################################
mysql> create table t5 (id1 timestamp);
query ok, 0 rows affected (0.02 sec)

mysql> desc t5;
+-------+-----------+------+-----+-------------------+-----------------------------+
| field | type      | null | key | default           | extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id1   | timestamp | no   |     | current_timestamp | on update current_timestamp |
+-------+-----------+------+-----+-------------------+-----------------------------+
row in set (0.00 sec)

# 插入数据null，会自动插入当前时间的时间
mysql> insert into t5 values (null);
query ok, 1 row affected (0.00 sec)

mysql> select * from t5;
+---------------------+
| id1                 |
+---------------------+
| 2018-09-21 14:56:50 |
+---------------------+
row in set (0.00 sec)

#添加一列 默认值是'0000-00-00 00:00:00'
mysql> alter table t5 add id2 timestamp;
query ok, 0 rows affected (0.02 sec)
records: 0  duplicates: 0  warnings: 0

mysql> show create table t5 \g;
*************************** 1. row ***************************
       table: t5
create table: create table `t5` (
  `id1` timestamp not null default current_timestamp on update current_timestamp,
  `id2` timestamp not null default '0000-00-00 00:00:00'
) engine=innodb default charset=utf8
row in set (0.00 sec)

error: 
no query specified

# 手动修改新的列默认值为当前时间
mysql> alter table t5 modify id2 timestamp default current_timestamp;
query ok, 0 rows affected (0.02 sec)
records: 0  duplicates: 0  warnings: 0

mysql> show create table t5 \g;
*************************** 1. row ***************************
       table: t5
create table: create table `t5` (
  `id1` timestamp not null default current_timestamp on update current_timestamp,
  `id2` timestamp not null default current_timestamp
) engine=innodb default charset=utf8
row in set (0.00 sec)

error: 
no query specified

mysql> insert into t5 values (null,null);
query ok, 1 row affected (0.01 sec)

mysql> select * from t5;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2018-09-21 14:56:50 | 0000-00-00 00:00:00 |
| 2018-09-21 14:59:31 | 2018-09-21 14:59:31 |
+---------------------+---------------------+
rows in set (0.00 sec)

timestamp示例
##################################################################
mysql> create table t6 (t1 timestamp);
query ok, 0 rows affected (0.02 sec)

mysql> desc t6;
+-------+-----------+------+-----+-------------------+-----------------------------+
| field | type      | null | key | default           | extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| t1    | timestamp | no   |     | current_timestamp | on update current_timestamp |
+-------+-----------+------+-----+-------------------+-----------------------------+
row in set (0.01 sec)

mysql> insert into t6 values (19700101080001);
query ok, 1 row affected (0.00 sec)

mysql> select * from t6;
+---------------------+
| t1                  |
+---------------------+
| 1970-01-01 08:00:01 |
+---------------------+
row in set (0.00 sec)
# timestamp时间的下限是19700101080001
mysql> insert into t6 values (19700101080000);
error 1292 (22007): incorrect datetime value: '19700101080000' for column 't1' at row 1

mysql> insert into t6 values ('2038-01-19 11:14:07');
query ok, 1 row affected (0.00 sec)
# timestamp时间的上限是2038-01-19 11:14:07
mysql> insert into t6 values ('2038-01-19 11:14:08');
error 1292 (22007): incorrect datetime value: '2038-01-19 11:14:08' for column 't1' at row 1
mysql> 

timestamp示例2
##################################################################
mysql> create table t7 (y year);
query ok, 0 rows affected (0.02 sec)

mysql> insert into t7 values (2018);
query ok, 1 row affected (0.00 sec)

mysql> select * from t7;
+------+
| y    |
+------+
| 2018 |
+------+
row in set (0.00 sec)

year示例
##################################################################
mysql> create table t8 (dt datetime);
query ok, 0 rows affected (0.01 sec)

mysql> insert into t8 values ('2018-9-26 12:20:10');
query ok, 1 row affected (0.01 sec)

mysql> insert into t8 values ('2018/9/26 12+20+10');
query ok, 1 row affected (0.00 sec)

mysql> insert into t8 values ('20180926122010');
query ok, 1 row affected (0.00 sec)

mysql> insert into t8 values (20180926122010);
query ok, 1 row affected (0.00 sec)

mysql> select * from t8;
+---------------------+
| dt                  |
+---------------------+
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
+---------------------+
rows in set (0.00 sec)

datetime示例

字符串类型

字符串类型指char、varchar、binary、varbinary、blob、text、enum和set。
该节描述了这些类型如何工作以及如何在查询中使用这些类型。

字符串类型表

类型	大小	用途
char	0-255字节	定长字符串
varchar	0-65535 字节	变长字符串
tinyblob	0-255字节	不超过 255 个字符的二进制字符串
tinytext	0-255字节	短文本字符串
blob	0-65 535字节	二进制形式的长文本数据
text	0-65 535字节	长文本数据
mediumblob	0-16 777 215字节	二进制形式的中等长度文本数据
mediumtext	0-16 777 215字节	中等长度文本数据
longblob	0-4 294 967 295字节	二进制形式的极大文本数据
longtext	0-4 294 967 295字节	极大文本数据

定长的单位 char(15)
- char ：浪费空间，存取效率相对高长度变化小的 (优先考虑使用)
变长的单位 varchar(15)
- varchar ：节省空间、存取效率相对低
create table 表(name1 char(5),name2 varchar(5));

# 手机号码、身份证号  char
# 用户名、密码  char
# 评论  微博 说说 微信状态 varchar
char 和 varchar 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。
在存储或检索过程中不进行大小写转换。
char列的长度固定为创建表是声明的长度,范围(0-255);而varchar的值是可变长字符串范围(0-65535)。

mysql的varchar定义长度到底是字节
varchar存储规则：
4.0版本以下，varchar(20)，指的是20字节，如果存放utf8汉字时，只能存6个（每个汉字3字节）
5.0版本以上，varchar(20)，指的是20字符，无论存放的是数字、字母还是utf8汉字（每个汉字3字节），
都可以存放20个，最大大小是65532字节
mysql4中最大也不过是20个字节,但是mysql5根据编码不同,存储大小也不同。

mysql> create table t9 (v varchar(4),c char(4));
query ok, 0 rows affected (0.01 sec)

mysql> insert into t9 values ('ab  ','ab  ');
query ok, 1 row affected (0.00 sec)

# 在检索的时候char数据类型会去掉空格
mysql> select * from t9;
+------+------+
| v    | c    |
+------+------+
| ab   | ab   |
+------+------+
row in set (0.00 sec)

# 来看看对查询结果计算的长度
mysql> select length(v),length(c) from t9;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         4 |         2 |
+-----------+-----------+
row in set (0.00 sec)

# 给结果拼上一个加号会更清楚
mysql> select concat(v,'+'),concat(c,'+') from t9;
+---------------+---------------+
| concat(v,'+') | concat(c,'+') |
+---------------+---------------+
| ab  +         | ab+           |
+---------------+---------------+
row in set (0.00 sec)

# 当存储的长度超出定义的长度，会截断
mysql> insert into t9 values ('abcd  ','abcd  ');
query ok, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t9;
+------+------+
| v    | c    |
+------+------+
| ab   | ab   |
| abcd | abcd |
+------+------+
rows in set (0.00 sec)

char/varchar示例

enum 枚举类型

enum中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显示。enum只允许从值集合中选取单个值，
而不能一次取多个值。
set和enum非常相似，也是一个字符串对象，里面可以包含0-64个成员。根据成员的不同，存储上也有所不同。
set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。

枚举类型表

类型	大小	用途
enum	对1-255个成员的枚举需要1个字节存储; 对于255-65535个成员，需要2个字节存储; 最多允许65535个成员。	单选：选择性别
set	1-8个成员的集合，占1个字节 9-16个成员的集合，占2个字节 17-24个成员的集合，占3个字节 25-32个成员的集合，
占4个字节 33-64个成员的集合，占8个字节	多选：兴趣爱好

create table 表(name char(12),gender enum('male','female'),hobby set('抽烟','喝酒','烫头','洗脚'));
insert into t12 values('gandan','不详','抽烟,喝酒,洗脚,洗脚,按摩');

mysql> create table t10 (name char(20),gender enum('female','male'));
query ok, 0 rows affected (0.01 sec)

# 选择enum('female','male')中的一项作为gender的值，可以正常插入
mysql> insert into t10 values ('goudan','male');
query ok, 1 row affected (0.00 sec)

# 不能同时插入'male,female'两个值，也不能插入不属于'male,female'的值
mysql> insert into t10 values ('goudan','male,female');
error 1265 (01000): data truncated for column 'gender' at row 1

mysql> create table t11 (name char(20),hobby set('抽烟','喝酒','烫头','翻车'));
query ok, 0 rows affected (0.01 sec)

# 可以任意选择set('抽烟','喝酒','烫头','翻车')中的项，并自带去重功能
mysql> insert into t11 values ('gandna','烫头,喝酒,烫头');
query ok, 1 row affected (0.01 sec)

mysql> select * from t11;
+------+---------------+
| name | hobby        |
+------+---------------+
| gandan | 喝酒,烫头     |
+------+---------------+
row in set (0.00 sec)

# 不能选择不属于set('抽烟','喝酒','烫头','翻车')中的项，
mysql> insert into t11 values ('gandan','烫头,翻车,看妹子');
error 1265 (01000): data truncated for column 'hobby' at row 1

set/enum示例

练习题

#建表 表结构为 id name gender(性别单选) hire_day(入职日期) salary float(8,2) hobby(爱好)

create table staff(id int,name char(12),gender enum('male','female'),hire_date date,salary float(8,2),hobby set('唱','跳','rap','打篮球'))

上一篇：合答安的一生有多传奇？年过四十做过奴隶，依旧被封为皇后

下一篇：凭啥敢卖7777？微星X570锐龙主板深度解析

mysql 支持的数据类型

mysql支持的数据类型

数值类型

数值类型表

有符合的8位前面第一位是表示符号的,后边只能显示7位

整数 int: create table 表 (id1 int(4),id2 int(11));

int默认是有符号的

int 默认11位整数

它能表示的数字的范围不被宽度约束#它只能约束数字的显示宽度

整数 int: create table 表 (id1 int unsigned,id2 int); #unsigned 指定取除int符号

小数 float: create table 表 (f1 float(5,2),d1 double(5,2)); #float单精度浮点数值 #double双精度浮点数值 (5,2)整数,小数范围限制

小数 float: create table 表 (f1 float,d1 double); 默认值

小数 float: create table 表(d1 decimal,d2 decimal(25,20));#decimal 小数值默认与指定(25,20)

日期类型

日期类型表

日期类型

字符串类型

字符串类型表

定长的单位 char(15)

变长的单位 varchar(15)

create table 表(name1 char(5),name2 varchar(5));

enum 枚举类型

枚举类型表

create table 表(name char(12),gender enum('male','female'),hobby set('抽烟','喝酒','烫头','洗脚'));

insert into t12 values('gandan','不详','抽烟,喝酒,洗脚,洗脚,按摩');

练习题

SlickEdit Core添加对Eclipse 3.5的支持

将MySQL命令行的显示数据提取为文本方法[图文]

简单的图形计数器需要MYSQL，GD的支持_MySQL

MySQL的MyISAM存储引擎修复及修改最大文件大小

php5.4以下版本json不支持不转义内容中文的解决方法_PHP

关系数据库的范式_MySQL

mysql数据库不能识别中文的查询条件。_MySQL

MySQL数据库的导入导出与Linux的权限

清空MySQL下的bin.00000mysql日志文件

mysql的事务运作

mysql 支持的数据类型

mysql支持的数据类型

数值类型

数值类型表

有符合的8位前面第一位是表示符号的,后边只能显示7位

整数 int: create table 表 (id1 int(4),id2 int(11));

int默认是有符号的

int 默认11位 整数

它能表示的数字的范围不被宽度约束#它只能约束数字的显示宽度

整数 int: create table 表 (id1 int unsigned,id2 int); #unsigned 指定取除int符号

小数 float: create table 表 (f1 float(5,2),d1 double(5,2)); #float单精度浮点数值 #double双精度浮点数值 (5,2)整数,小数范围限制

小数 float: create table 表 (f1 float,d1 double); 默认值

小数 float: create table 表(d1 decimal,d2 decimal(25,20));#decimal 小数值 默认与指定(25,20)

日期类型

日期类型表

日期类型

字符串类型

字符串类型表

定长的单位 char(15)

变长的单位 varchar(15)

create table 表(name1 char(5),name2 varchar(5));

enum 枚举类型

枚举类型表

create table 表(name char(12),gender enum('male','female'),hobby set('抽烟','喝酒','烫头','洗脚'));

insert into t12 values('gandan','不详','抽烟,喝酒,洗脚,洗脚,按摩');

练习题

SlickEdit Core添加对Eclipse 3.5的支持

将MySQL命令行的显示数据提取为文本方法[图文]

简单的图形计数器需要MYSQL，GD的支持_MySQL

MySQL的MyISAM存储引擎修复及修改最大文件大小

php5.4以下版本json不支持不转义内容中文的解决方法_PHP

关系数据库的范式_MySQL

mysql数据库不能识别中文的查询条件。_MySQL

MySQL数据库的导入导出与Linux的权限

清空MySQL下的bin.00000mysql日志文件

mysql的事务运作

int 默认11位整数

小数 float: create table 表(d1 decimal,d2 decimal(25,20));#decimal 小数值默认与指定(25,20)