1

MySQL学习笔记：数据类型-字段选型

 1 year ago
source link: https://blog.51cto.com/u_16070827/6235377
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

MySQL学习笔记：数据类型-字段选型

精选 原创

chenlbbb 2023-04-28 18:55:58 博主文章分类：MySQL ©著作权

文章标签 mysql 数据类型 插入图片 文章分类 MySQL 数据库 阅读数186

学习SQL语句，建表的时候，离不开的就是给字段指定数据类型
那么mysql数据库中有哪些数据类型呢？
这些数据类型的应用场景又是怎样的呢？
希望本博客对你有帮助！

我们先从官网文档里看看有哪些数据类型

MySQL学习笔记：数据类型-字段选型_插入图片

整理的数据类型如下：

MySQL学习笔记：数据类型-字段选型_插入图片_02

数据类型还是非常多，由于篇幅有限，我会介绍前三大数据类型，数值型、日期和时间类型、字符串类型哦！

数值型包括以下：

MySQL学习笔记：数据类型-字段选型_mysql_03
  • tinyint 1个字节，8位，常用于数值较小的数，无符号0-255，有符号-128 - 127
  • smallint 2个字节，16位，无符号0-65535，有符号-32768 - 32767
  • mediumint 3个字节，24位
  • int 4个字节，32位
  • bigint 8个字节，64位

TINYINT

示例：创建t2表，增加id字段，数据类型为tinyint，默认使用signed有符号数-128~127
root@chen 02:44  mysql>create table t2(id tinyint);
Query OK, 0 rows affected (0.02 sec)

root@chen 02:44  mysql>desc t2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

root@chen 02:44  mysql>insert into t2(id) values(100);
Query OK, 1 row affected (0.00 sec)

root@chen 02:44  mysql>insert into t2(id) values(130);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
root@chen 02:45  mysql>insert into t2(id) values(127);
Query OK, 1 row affected (0.00 sec)

root@chen 02:45  mysql>insert into t2(id) values(128);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
示例：创建t3表，使用无符号数0~256
root@chen 02:45  mysql>create table t3(id tinyint unsigned);
Query OK, 0 rows affected (0.01 sec)

root@chen 02:47  mysql>desc t3;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

root@chen 02:47  mysql>insert into t3(id) values(130);
Query OK, 1 row affected (0.00 sec)

root@chen 02:48  mysql>insert into t3(id) values(300);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
示例：创建表t4，数据类型int，增加电话号码
root@chen 02:53  mysql>create table t4(id int);
Query OK, 0 rows affected (0.01 sec)

root@chen 02:55  mysql>insert into t4(id) values(18174458104);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

默认有符号数，电话号码值过大，int不宜存放电话号码

示例：创建表t5，数据类型varchar，存电话号码
root@chen 02:55  mysql>create table t5(id varchar(20));
Query OK, 0 rows affected (0.05 sec)

root@chen 02:58  mysql>insert into t5(id) values(18174458104);
Query OK, 1 row affected (0.00 sec)

root@chen 03:00  mysql>select * from t5;
+-------------+
| id          |
+-------------+
| 18174458104 |
+-------------+
1 row in set (0.00 sec)

定点型decimal

特点：数值精确

常用于审计，算账等钱财不能出一点错的场景

root@chen 03:00  mysql>create table salary(id int(5),name varchar(20),salary decimal(10,2));
Query OK, 0 rows affected (0.02 sec)

root@chen 03:16  mysql>desc salary;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int(5)        | YES  |     | NULL    |       |
| name   | varchar(20)   | YES  |     | NULL    |       |
| salary | decimal(10,2) | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

decimal(10,2)，总共10位，整数占8位，小数占2位，如19923124.12

适用于存放字符型数据

  • char 定长字符串
  • varchar 可变长字符串
  • text 可存放文本数据
  • blob 存放二进制文本，如图片、视频、音频
  • enum 枚举，指定选项，固定选择
  • set 集合，指定选项，内容只能由一个或多个组成

char和varchar的区别

MySQL学习笔记：数据类型-字段选型_数据类型_04

char：character 固定长度字符串

varchar： variable character 可变长度字符串，存储会在最后多加一个空格。

  1. char（len）括号中存储写的是字符长度，最大值为255，如果在存储的时候实际存储的字符串长度小于括号中的长度，那它在存储的时候会以空格补全位数进行存储；
  2. varchar（len），最大长度取值为65535，不会空格补全进行存储；

取数据区别

char在取值的时候会把存储后面的空格去掉

varchar和char类型在读取数据的时候，都会删除自动填充的空格。

存储大小区别

char类型最大可存储255个字节

varchar类型可存储65535个字节

text型

MySQL学习笔记：数据类型-字段选型_数据类型_05

blob型

MySQL学习笔记：数据类型-字段选型_插入图片_06

图片视频音频，一般不用blob型存储，一般只存储一个url或者是路径

enum枚举型

插入数据时，enum数据类型的字段必须从枚举选项中选择。

An is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time. ENUM

root@lianxi 11:10  mysql>CREATE TABLE shirts (
    ->     name VARCHAR(40),
    ->     size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
    -> );
Query OK, 0 rows affected (0.01 sec)

root@lianxi 11:11  mysql>desc shirts;
+-------+----------------------------------------------------+------+-----+---------+-------+
| Field | Type                                               | Null | Key | Default | Extra |
+-------+----------------------------------------------------+------+-----+---------+-------+
| name  | varchar(40)                                        | YES  |     | NULL    |       |
| size  | enum('x-small','small','medium','large','x-large') | YES  |     | NULL    |       |
+-------+----------------------------------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

root@lianxi 11:11  mysql>INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
    ->   ('polo shirt','small');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@lianxi 11:12  mysql>select * from shirts;
+-------------+--------+
| name        | size   |
+-------------+--------+
| dress shirt | large  |
| t-shirt     | medium |
| polo shirt  | small  |
+-------------+--------+
3 rows in set (0.00 sec)

set集合型

可以有零的字符串对象 或更多值，每个值都必须从 创建表时指定的允许值。 由多个组成的列值 集合成员由成员指定，成员之间用逗号分隔 ().

root@lianxi 11:21  mysql>CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
Query OK, 0 rows affected (0.04 sec)

root@lianxi 11:21  mysql>desc myset;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| col   | set('a','b','c','d') | YES  |     | NULL    |       |
+-------+----------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

root@lianxi 11:22  mysql>INSERT INTO myset (col) VALUES   ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

root@lianxi 11:22  mysql>select * from myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
5 rows in set (0.00 sec)

日期和时间型

  • year 年份
  • time 时分秒
  • datetime 年月日时分秒
  • timestamp 年月日时分秒
  • date 年月日
MySQL学习笔记：数据类型-字段选型_数据类型_07
MySQL学习笔记：数据类型-字段选型_mysql_08

常用日期时间类型–timestamp型

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

timestamp消耗4个字节，2^32，换算成秒，从1970-01-01 00：00：01开始，正好算到2038年。

root@lianxi 11:42  mysql>create table t1(id int,name varchar(20),birth timestamp);
Query OK, 0 rows affected (0.01 sec)

root@lianxi 11:43  mysql>desc t1;
+-------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type        | Null | Key | Default           | Extra                       |
+-------+-------------+------+-----+-------------------+-----------------------------+
| id    | int(11)     | YES  |     | NULL              |                             |
| name  | varchar(20) | YES  |     | NULL              |                             |
| birth | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)

root@lianxi 11:46  mysql>insert into t1(id,name,birth) values(1,'chenlibiao','2001-12-29 12:34:02');
Query OK, 1 row affected (0.00 sec)

root@lianxi 11:47  mysql>select * from t1;
+------+------------+---------------------+
| id   | name       | birth               |
+------+------------+---------------------+
|    1 | chenlibiao | 2001-12-29 12:34:02 |
+------+------------+---------------------+
1 row in set (0.00 sec)

root@lianxi 11:47  mysql>insert into t1(id,name,birth) values(1,'felix',now());
Query OK, 1 row affected (0.01 sec)

root@lianxi 11:47  mysql>select * from t1;
+------+------------+---------------------+
| id   | name       | birth               |
+------+------------+---------------------+
|    1 | chenlibiao | 2001-12-29 12:34:02 |
|    1 | felix      | 2023-04-19 11:47:37 |
+------+------------+---------------------+
2 rows in set (0.00 sec)

now()函数，获取当前日期时间。

由于篇幅有限，很多数据类型就不详细介绍啦
有需要的读者，可以查阅官网文档哦
官方文档
 https://dev.mysql.com/doc/refman/8.0/en/


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK