mysql基础数据类型118-124

mysql基础数据类型118-124,第1张

#常见的数据类型

/*

数值型:

    整形

    小数:

        定点数

        浮点数

字符型:

    较短的文本:char、varchar

    较长的文本:text、blob(较长的二进制数据)

日期型:

*/

#一、整形

/*

分类:

tinyint、smallint、mediumint、int/integer、bigint

1            2                3                        4                8

特点:

    -如果不设置无符号还是有符号,默认是有符号,如果想设置无符号,需要添加unsigned关键字

    -如果插入的数值超出了整形的范围,会报out of range异常,并且插入临界值

    -如果不设置长度,会有默认的长度

    -长度代表了显示的最大宽度,如果不够会用0在左边填充,但是必须搭配zerofill使用

*/

#1.如何设置无符号和有符号

CREATE TABLE tab_int(

    t1 INT

    t2 INT ZEROFILL  【无符号】

)

#二、小数

/*

浮点型

    float(M, D)

    double(M, D)

定点型

    dec(M, D)

    decimal(M, D)

特点:

-M:整数部位+小数部位

-D:小数部位

-M和D都可以省略

如果是decimal,则M默认为10,D默认为0

如果是float和double,则会根据插入的数值的精度来决定精度

定点型的精确度较高,如果要求插入数值的精度较高如货币运算等则考虑使用

*/

CREATE TABLE tab_float (

    f1 FLOAT(5, 2)

    f2 DOUBLE (5, 2)

    f3 DECIMAL(5, 2)

)

#原则:

/*

所选择的类型越简单越好,能保存数值的类型越小越好

*/

#三、字符型

/*

较短的文本:

char

varchar

较长的文本:

text

blob(较大的二进制)

特点:

                    写法                M的意思                                         特点                       空间的耗费                    效率        

char            char(M)       最大的字符数,可以省略         固定长度的字符            比较耗费                        高

varchar      carchar(M)    最大的字符数,不可以省略    可变长度的字符            比较节省                        低

*/

CREATE TABLE tab_char(

    c1 ENUM('a','b','c')

)

CREATE TABLE tab_set(

    s1    SET ('a','b','c','d')

)

#四、日期型

特点:

                                     字节                        范围                    时区等的影响

datetime                          8                            10000-9999            不受

timestamp                        4                        1970-2038                    受

CREATE TABLE tab_date(

    t1    DATETIME,

    t2    TIMESTAMP

)

#常见约束

/*

含义:一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性

分类:六大约束

    NOT NULL:非空,用于保证该字段的值不能为空,比如姓名、学号等

    default:默认,用于保证该字段有默认值,比如性别

    primary key:主键,用于保证该字段的值具有唯一性,并且非空,比如学号等

    unique:唯一,用于保证该字段的值具有唯一性,可以为空,比如座位号

    check:检查约束【mysql中不支持】,比如年龄、性别

    foreign key:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值,在从表添加外键约束,用于引用主表中某列的值,比如员工表的部门编号,员工表的工种编号

添加约束的时机:

    1.创建表时

    2.修改表时

约束的添加分类:

    列级约束:

        六大约束语法上都支持,但外键约束没有效果

    表级约束:

        除了非空、默认,其他的都支持

*/

CREATE TABLE 表名(

    字段名    字段类型    列级约束,

    表级约束

)

#一、创建表时添加约束

/*

语法:

直接在字段名和类型后面追加 约束类型即可

只支持:

*/

#1.添加列级约束

create table stuinfo (

    id int primary key,  #主键

    stuName  varchar(20) not null,   #非空

    gender char(1)  check(gender='男' OR gender ='女'),  #检查约束

    seat int unique,  #唯一约束

    age  int  default  18,  #默认约束

    majorId int foreign key references major (id)  #外键

)

create table major (

    id int primary key,

    majorName  varchar (20)

)

desc stuinfo   #查看表结构

show index from stuinfo  #查看stuinfo表中所有的索引,包括主键、外键、唯一

#2、添加表级约束

create table stuinfo (

     id int

    stuName  varchar(20) 

    gender char(1) 

    seat int,

    age  int  , 

    majorId int

    constraint pk primary key (id),  #主键

    constraint  uq unique (seat),  #唯一键

    constraint  ck check (gender = '男' or gender = '女'),   #检查

    constraint  fk_stuinfo_major foreign key(majorid) references major(id)  #外键

)

MySQL 数据类型细分下来,大概有以下几类:

数值,典型代表为 tinyint,int,bigint

浮点/定点,典型代表为 float,double,decimal 以及相关的同义词

字符串,典型代表为 char,varchar

时间日期,典型代表为 date,datetime,time,timestamp

二进制,典型代表为 binary,varbinary

位类型

枚举类型

集合类型

大对象,比如 text,blob

json 文档类型

一、数值类型(不是数据类型,别看错了)如果用来存放整数,根据范围的不同,选择不同的类型。

以上是几个整数选型的例子。整数的应用范围最广泛,可以用来存储数字,也可以用来存储时间戳,还可以用来存储其他类型转换为数字后的编码,如 IPv4 等。示例 1用 int32 来存放 IPv4 地址,比单纯用字符串节省空间。表 x1,字段 ipaddr,利用函数 inet_aton,检索的话用函数 inet_ntoa。

查看磁盘空间占用,t3 占用最大,t1 占用最小。所以说如果整数存储范围有固定上限,并且未来也没有必要扩容的话,建议选择最小的类型,当然了对其他类型也适用。root@ytt-pc:/var/lib/mysql/3305/ytt# ls -sihl总用量 3.0G3541825 861M -rw-r----- 1 mysql mysql 860M 12月 10 11:36 t1.ibd3541820 989M -rw-r----- 1 mysql mysql 988M 12月 10 11:38 t2.ibd3541823 1.2G -rw-r----- 1 mysql mysql 1.2G 12月 10 11:39 t3.ibd

二、浮点数 / 定点数先说 浮点数,float 和 double 都代表浮点数,区别简单记就是 float 默认占 4 Byte。float(p) 中的 p 代表整数位最小精度。如果 p >24 则直接转换为 double,占 8 Byte。p 最大值为 53,但最大值存在计算不精确的问题。再说 定点数,包括 decimal 以及同义词 numeric,定点数的整数位和小数位分别存储,有效精度最大不能超过 65。所以区别于 float 的在于精确存储,必须需要精确存储或者精确计算的最好定义为 decimal 即可。示例 3创建一张表 y1,分别给字段 f1,f2,f3 不同的类型。mysql-(ytt/3305)->create table y1(f1 float,f2 double,f3 decimal(10,2))Query OK, 0 rows affected (0.03 sec)

三、字符类型字符类型和整形一样,用途也很广。用来存储字符、字符串、MySQL 所有未知的类型。可以简单说是万能类型!

char(10) 代表最大支持 10 个字符存储,varhar(10) 虽然和 char(10) 可存储的字符数一样多,不同的是 varchar 类型存储的是实际大小,char 存储的理论固定大小。具体的字节数和字符集相关。示例 4例如下面表 t4 ,两个字段 c1,c2,分别为 char 和 varchar。mysql-(ytt/3305)->create table t4 (c1 char(20),c2 varchar(20))Query OK, 0 rows affected (0.02 sec)

所以在 char 和 varchar 选型上,要注意看是否合适的取值范围。比如固定长度的值,肯定要选择 char;不确定的值,则选择 varchar。

四、日期类型日期类型包含了 date,time,datetime,timestamp,以及 year。year 占 1 Byte,date 占 3 Byte。 

time,timestamp,datetime 在不包含小数位时分别占用 3 Byte,4 Byte,8 Byte;小数位部分另外计算磁盘占用,见下面表格。

请点击输入图片描述

请点击输入图片描述

请点击输入图片描述

注意:timestamp 代表的时间戳是一个 int32 存储的整数,取值范围为 '1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999';datetime 取值范围为 '1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。

综上所述,日期这块类型的选择遵循以下原则:

1. 如果时间有可能超过时间戳范围,优先选择 datetime。2. 如果需要单独获取年份值,比如按照年来分区,按照年来检索等,最好在表中添加一个 year 类型来参与。3. 如果需要单独获取日期或者时间,最好是单独存放,而不是简单的用 datetime 或者 timestamp。后面检索时,再加函数过滤,以免后期增加 SQL 编写带来额外消耗。

4. 如果有保存毫秒类似的需求,最好是用时间类型自己的特性,不要直接用字符类型来代替。MySQL 内部的类型转换对资源额外的消耗也是需要考虑的。

示例 5

建立表 t5,对这些可能需要的字段全部分离开,这样以后写 SQL 语句的时候就很容易了。

当然了,这种情形占用额外的磁盘空间。如果想在易用性与空间占用量大这两点来折中,可以用 MySQL 的虚拟列来实时计算。比如假设 c5 字段不存在,想要得到 c5 的结果。mysql-(ytt/3305)->alter table t5 drop c5, add c5 year generated always as (year(c1)) virtualQuery OK, 1 row affected (2.46 sec)Records: 1  Duplicates: 0  Warnings: 0

五、二进制类型

binary 和 varbinary 对应了 char 和 varchar 的二进制存储,相关的特性都一样。不同的有以下几点:

binary(10)/varbinary(10) 代表的不是字符个数,而是字节数。

行结束符不一样。char 的行结束符是 \0,binary 的行结束符是 0x00。

由于是二进制存储,所以字符编码以及排序规则这类就直接无效了。

示例 6

来看这个 binary 存取的简单示例,还是之前的变量 @a。

切记!这里要提前计算好 @a 占用的字节数,以防存储溢出。

六、位类型

bit 为 MySQL 里存储比特位的类型,最大支持 64 比特位, 直接以二进制方式存储,一般用来存储状态类的信息。比如,性别,真假等。具有以下特性:

1. 对于 bit(8) 如果单纯存放 1 位,左边以 0 填充 00000001。2. 查询时可以直接十进制来过滤数据。3. 如果此字段加上索引,MySQL 不会自己做类型转换,只能用二进制来过滤。

示例 7

创建表 c1, 字段性别定义一个比特位。mysql-(ytt/3305)->create table c1(gender bit(1))Query OK, 0 rows affected (0.02 sec)

mysql-(ytt/3305)->select cast(gender as unsigned)  'f1' from c1+------+| f1   |+------+|    0 ||    1 |+------+2 rows in set (0.00 sec)

过滤数据也一样,二进制或者直接十进制都行。mysql-(ytt/3305)->select conv(gender,16,10) as gender \   ->from c1 where gender = b'1' +--------+| gender |+--------+| 1      |+--------+1 row in set (0.00 sec)    mysql-(ytt/3305)->select conv(gender,16,10) as gender \    ->from c1 where gender = '1'+--------+| gender |+--------+| 1      |+--------+1 row in set (0.00 sec)

其实这样的场景,也可以定义为 char(0),这也是类似于 bit 非常优化的一种用法。

mysql-(ytt/3305)->create table c2(gender char(0))Query OK, 0 rows affected (0.03 sec)

那现在我给表 c1 简单的造点测试数据。

mysql-(ytt/3305)->select count(*) from c1+----------+| count(*) |+----------+| 33554432 |+----------+1 row in set (1.37 sec)

把 c1 的数据全部插入 c2。

mysql-(ytt/3305)->insert into c2 select if(gender = 0,'',null) from c1Query OK, 33554432 rows affected (2 min 18.80 sec)Records: 33554432  Duplicates: 0  Warnings: 0

两张表的磁盘占用差不多。root@ytt-pc:/var/lib/mysql/3305/ytt# ls -sihl总用量 1.9G4085684 933M -rw-r----- 1 mysql mysql 932M 12月 11 10:16 c1.ibd4082686 917M -rw-r----- 1 mysql mysql 916M 12月 11 10:22 c2.ibd

检索方式稍微有些不同,不过效率也差不多。所以说,字符类型不愧为万能类型。

七、枚举类型

枚举类型,也即 enum。适合提前规划好了所有已经知道的值,且未来最好不要加新值的情形。枚举类型有以下特性:

1. 最大占用 2 Byte。2. 最大支持 65535 个不同元素。3. MySQL 后台存储以下标的方式,也就是 tinyint 或者 smallint 的方式,下标从 1 开始。4. 排序时按照下标排序,而不是按照里面元素的数据类型。所以这点要格外注意。

示例 8

创建表 t7。mysql-(ytt/3305)->create table t7(c1 enum('mysql','oracle','dble','postgresql','mongodb','redis','db2','sql server'))Query OK, 0 rows affected (0.03 sec)

八、集合类型

集合类型 SET 和枚举类似,也是得提前知道有多少个元素。SET 有以下特点:

1. 最大占用 8 Byte,int64。2. 内部以二进制位的方式存储,对应的下标如果以十进制来看,就分别为 1,2,4,8,...,pow(2,63)。3. 最大支持 64 个不同的元素,重复元素的插入,取出来直接去重。4. 元素之间可以组合插入,比如下标为 1 和 2 的可以一起插入,直接插入 3 即可。

示例 9

定义表 c7 字段 c1 为 set 类型,包含了 8 个值,也就是下表最大为 pow(2,7)。

mysql-(ytt/3305)->create table c7(c1 set('mysql','oracle','dble','postgresql','mongodb','redis','db2','sql server'))Query OK, 0 rows affected (0.02 sec)

插入 1 到 128 的所有组合。

mysql-(ytt/3305)->INSERT INTO c7WITH RECURSIVE ytt_number (cnt) AS (        SELECT 1 AS cnt        UNION ALL        SELECT cnt + 1        FROM ytt_number        WHERE cnt <pow(2, 7)    )SELECT *FROM ytt_numberQuery OK, 128 rows affected (0.01 sec)Records: 128  Duplicates: 0  Warnings: 0

九、数据类型在存储函数中的用法

函数里除了显式声明的变量外,默认 session 变量的数据类型很弱,随着给定值的不同随意转换。

示例 10

定义一个函数,返回两个给定参数的乘积。定义里有两个变量,一个是 v_tmp 显式定义为 int64,另外一个 @vresult 随着给定值的类型随意变换类型。

简单调用下。

mysql-(ytt/3305)->select ytt_sample_data_type(1111,222) 'result'+--------------------------+| result                   |+--------------------------+| The result is: '246642'. |+--------------------------+1 row in set (0.00 sec)

总结

本篇把 MySQL 基本的数据类型做了简单的介绍,并且用了一些容易理解的示例来梳理这些类型。我们在实际场景中,建议选择适合最合适的类型,不建议所有数据类型简单的最大化原则。比如能用 varchar(100),不用 varchar(1000)。


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/5907719.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-03-07
下一篇 2023-03-07

发表评论

登录后才能评论

评论列表(0条)

保存