MySQL学习--列类型_字符类型 vchar-CSDN博客

本文链接：https://blog.csdn.net/qq_43710889/article/details/115434792

文章目录

- MySQL学习--列类型
列类型

MySQL学习–列类型

列类型

1.数值类型

整数类型

类型	字节	最小值（带符号/无符号）	最大值（带符号/无符号）
Tinyint	1	-128	127
		0	255
Smallint	2	-32768	32767
		0	65535
mediumint	3	-8388608	8388607
		0	16777215
int	4	-2147483648	2147483647
		0	4294967295
bigint	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

整数类型三个参数：

unsigned：表示无符号位

（M）：表示补0宽度，与zerofill配合使用

zerofill：填充0，（默认zerofill就是unsigned类型）

例子：

mysql> use an
Database changed

mysql> create table class(
    -> sname varchar(20) not null default '',
    -> age tinyint not null default 0
    -> )engine myisam charset utf8;
Query OK, 0 rows affected, 1 warning (0.82 sec)

mysql> desc class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sname | varchar(20) | NO   |     |         |       |
| age   | tinyint     | NO   |     | 0       |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.13 sec)

mysql> insert into class(sname,age)
    -> values
    -> ('花花',24),
    -> ('啦啦',-128);
Query OK, 2 rows affected (0.32 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from class;
+--------+------+
| sname  | age  |
+--------+------+
| 花花   |   24 |
| 啦啦   | -128 |
+--------+------+
2 rows in set (0.10 sec)

mysql> alter table class add score tinyint unsigned not null default 0;
Query OK, 2 rows affected (1.23 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc class;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| sname | varchar(20)      | NO   |     |         |       |
| age   | tinyint          | NO   |     | 0       |       |
| score | tinyint unsigned | NO   |     | 0       |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.11 sec)

mysql> insert into class
    -> (sname,age,score)
    -> values
    -> ('暗暗',18,255),
    -> ('嘿嘿',79,12);
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from class;
+--------+------+-------+
| sname  | age  | score |
+--------+------+-------+
| 花花   |   24 |     0 |
| 啦啦   | -128 |     0 |
| 暗暗   |   18 |   255 |
| 嘿嘿   |   79 |    12 |
+--------+------+-------+
4 rows in set (0.00 sec)

mysql> desc class;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| sname | varchar(20)      | NO   |     |         |       |
| age   | tinyint          | NO   |     | 0       |       |
| score | tinyint unsigned | NO   |     | 0       |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> alter table class add age1 tinyint (1) not null default 0;
Query OK, 4 rows affected, 1 warning (1.12 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> desc class;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| sname | varchar(20)      | NO   |     |         |       |
| age   | tinyint          | NO   |     | 0       |       |
| score | tinyint unsigned | NO   |     | 0       |       |
| age1  | tinyint(1)       | NO   |     | 0       |       |
+-------+------------------+------+-----+---------+-------+
4 rows in set (0.10 sec)

mysql> insert into class(sname,age1) values ('吕布',99);
Query OK, 1 row affected (0.12 sec)


mysql> select * from class;
+--------+------+-------+------+
| sname  | age  | score | age1 |
+--------+------+-------+------+
| 花花   |   24 |     0 |    0 |
| 啦啦   | -128 |     0 |    0 |
| 暗暗   |   18 |   255 |    0 |
| 嘿嘿   |   79 |    12 |    0 |
| 吕布   |    0 |     0 |   99 |
+--------+------+-------+------+
5 rows in set (0.00 sec)

mysql> alter table class add snum smallint(5) zerofill not null default 0;
Query OK, 5 rows affected, 2 warnings (0.47 sec)
Records: 5  Duplicates: 0  Warnings: 2

mysql> select * from class;
+--------+------+-------+------+-------+
| sname  | age  | score | age1 | snum  |
+--------+------+-------+------+-------+
| 花花   |   24 |     0 |    0 | 00000 |
| 啦啦   | -128 |     0 |    0 | 00000 |
| 暗暗   |   18 |   255 |    0 | 00000 |
| 嘿嘿   |   79 |    12 |    0 | 00000 |
| 吕布   |    0 |     0 |   99 | 00000 |
+--------+------+-------+------+-------+
5 rows in set (0.00 sec)

mysql> insert into class(sname,snum) values ('牛逼',15),('爽爽',1);
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0


mysql> select * from class;
+--------+------+-------+------+-------+
| sname  | age  | score | age1 | snum  |
+--------+------+-------+------+-------+
| 花花   |   24 |     0 |    0 | 00000 |
| 啦啦   | -128 |     0 |    0 | 00000 |
| 暗暗   |   18 |   255 |    0 | 00000 |
| 嘿嘿   |   79 |    12 |    0 | 00000 |
| 吕布   |    0 |     0 |   99 | 00000 |
| 牛逼   |    0 |     0 |    0 | 00015 |
| 爽爽   |    0 |     0 |    0 | 00001 |
+--------+------+-------+------+-------+
7 rows in set (0.00 sec)

mysql> desc class;
+-------+-------------------------------+------+-----+---------+-------+
| Field | Type                          | Null | Key | Default | Extra |
+-------+-------------------------------+------+-----+---------+-------+
| sname | varchar(20)                   | NO   |     |         |       |
| age   | tinyint                       | NO   |     | 0       |       |
| score | tinyint unsigned              | NO   |     | 0       |       |
| age1  | tinyint(1)                    | NO   |     | 0       |       |
| snum  | smallint(5) unsigned zerofill | NO   |     | 00000   |       |
+-------+-------------------------------+------+-----+---------+-------+
5 rows in set (0.10 sec)

浮点、定点类型

float（M，D）；

M表示：精度，保存值的主要位数；
D表示：标度，小数点后面的位数；

float能占的空间：-10^38~1038

如果M<=24，占4个字节，否则占8个字节

decimal（M，D）；

定点：把整数部分和小数部分分开存储，比float更精确

mysql> create table account(
    -> id tinyint unsigned not null default 0,
    -> acc1 float(9,2) not null default 0,
    -> acc2 decimal(9,2) not null default 0
    -> )engine myisam charset utf8;
Query OK, 0 rows affected, 2 warnings (0.22 sec)

mysql>
mysql> insert into account values (1,1234567.23,1234567.25);
Query OK, 1 row affected (0.19 sec)

mysql> insert into account values (1,1234567.23,1234567.23);
Query OK, 1 row affected (0.12 sec)

mysql> select * from account;
+----+------------+------------+
| id | acc1       | acc2       |
+----+------------+------------+
|  1 | 1234567.25 | 1234567.25 |
|  1 | 1234567.25 | 1234567.23 |
+----+------------+------------+
2 rows in set (0.00 sec)

2.字符串类型

varchar（）、char（）类型

varchar(M)可变长类型、char(M)定长类型:M表示宽度

类型	宽度	可存字符	实存字符	实占空间	利用率
char	M	M	i	M	i/M=<100%
varchar	M	M	i	i+（1-2字节）	i/i+（1-2字节）<100%

char型：如果不够M个字符，内部用空格补齐，取出时再把右侧空格删掉

注：如果右侧本身有空格，将丢失。

vchar，char限制的是字符，不是字节。

mysql> create table vchar(
    -> name1 char(6) not null default '',
    -> name2 varchar(6) not null default ''
    -> )engine myisam charset utf8;
Query OK, 0 rows affected, 1 warning (0.22 sec)

mysql> insert into vchar values ('hello!','hello!');
Query OK, 1 row affected (0.20 sec)

mysql> insert into vchar values ('aa','aa');
Query OK, 1 row affected (0.04 sec)

mysql> select * from vchar;
+--------+--------+
| name1  | name2  |
+--------+--------+
| hello! | hello! |
| aa     | aa     |
+--------+--------+
2 rows in set (0.00 sec)

mysql> select concat(name1,'!'),concat(name2,'!') from vchar;
+-------------------+-------------------+
| concat(name1,'!') | concat(name2,'!') |
+-------------------+-------------------+
| hello!!           | hello!!           |
| aa!               | aa!               |
+-------------------+-------------------+
2 rows in set (0.11 sec)

mysql> insert into vchar values ('bb ','bb ');
Query OK, 1 row affected (0.11 sec)

mysql> select concat(name1,'!'),concat(name2,'!') from vchar;
+-------------------+-------------------+
| concat(name1,'!') | concat(name2,'!') |
+-------------------+-------------------+
| hello!!           | hello!!           |
| aa!               | aa!               |
| bb!               | bb !              |
+-------------------+-------------------+
3 rows in set (0.00 sec)

text类型

文本类型，可以存比较大的文本段，搜索速度慢；

如果内容不是特别大，建议使用char，varchar（text类型无需加默认值，加了也无效）

blob类型

二进制类型，用来存储图像，音频等二进制数据。

意义： 2进制,0-255都有可能出现。

Blob在于防止因为字符集的问题，导致信息丢失。比如:一张图片中有0xFF字节,这个在asci字符集认为非法在入库的时候,被过滤了。

mysql> create table txt1(
    -> article text
    -> );
Query OK, 0 rows affected (2.65 sec)

mysql> alter table txt1 add img blob;
Query OK, 0 rows affected (1.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc txt1;
+---------+------+------+-----+---------+-------+
| Field   | Type | Null | Key | Default | Extra |
+---------+------+------+-----+---------+-------+
| article | text | YES  |     | NULL    |       |
| img     | blob | YES  |     | NULL    |       |
+---------+------+------+-----+---------+-------+
2 rows in set (0.33 sec)

mysql> insert into txt1 values ('哈哈哈哈嗲互动艾斯比滴啊上','鹿鹿鱼鱼链路');
Query OK, 1 row affected (0.14 sec)

mysql> select * from txt1 ;
+-----------------------------------------+----------------------------------------+
| article                                 | img                                    |
+-----------------------------------------+----------------------------------------+
| 哈哈哈哈嗲互动艾斯比滴啊上              | 0xE9B9BFE9B9BFE9B1BCE9B1BCE993BEE8B7AF |
+-----------------------------------------+----------------------------------------+
1 row in set (0.00 sec)

3.日期/时间类型

几种类型比较如下：

日期时间类型	占用空间	日期格式	最小值	最大值	零值表示
DATETIME	8 bytes	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00-00 00:00:00
TIMESTAMP	4 bytes	YYYY-MM-DD HH:MM:SS	19700101080001	2038 年的某个时刻	00000000000000
DATE	4 bytes	YYYY-MM-DD	1000-01-01	9999-12-31	0000-00-00
TIME	3 bytes	HH:MM:SS	-838:59:59	838:59:59	00:00:00
YEAR	1 bytes	YYYY	1901	2155	0000

data类型

DATE 用于表示年月日，如果实际应用值需要保存年月日就可以使用 DATE。

time类型

TIME 用于表示时分秒，如果实际应用值需要保存时分秒就可以使用 TIME

datatime类型

DATETIME 用于表示年月日时分秒，是 DATE 和 TIME 的组合，并且记录的年份（见上表）比较长久。如果实际应用中有这样的需求，就可以使用 DATETIME 类型。

year类型

YEAR 用于表示年份，YEAR 有 2 位（最好使用4位）和 4 位格式的年。默认是4位。如果实际应用只保存年份，那么用 1 bytes 保存 YEAR 类型完全可以。不但能够节约存储空间，还能提高表的操作效率。

timestamp类型

TIMESTAMP 用于表示年月日时分秒，但是记录的年份（见上表）比较短暂。
TIMESTAMP 和时区相关，更能反映当前时间。当插入日期时，会先转换为本地时区后再存放；当查询日期时，会将日期转换为本地时区后再显示。所以不同时区的人看到的同一时间是不一样的。
表中的第一个 TIMESTAMP 列自动设置为系统时间（CURRENT_TIMESTAMP）。当插入或更新一行，但没有明确给 TIMESTAMP 列赋值，也会自动设置为当前系统时间。如果表中有第二个 TIMESTAMP 列，则默认值设置为0000-00-00 00:00:00。
TIMESTAMP 的属性受 Mysql 版本和服务器 SQLMode 的影响较大。

mysql> create table test01(
    -> sname varchar(20) not null default ''
    -> )engine myisam charset utf8;
Query OK, 0 rows affected, 1 warning (0.26 sec)

mysql> alter table test01 add birth date not null default '0000-00-00';
ERROR 1067 (42000): Invalid default value for 'birth'

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.70 sec)

mysql> set @@sql_mode=(select replace(@@sql_mode,'NO_ZERO_IN_DATE,NO_ZERO_DATE',''));
Query OK, 0 rows affected, 1 warning (0.18 sec)

mysql> select @@global.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set @@global.sql_mode=(select replace(@@global.sql_mode,'NO_ZERO_IN_DATE,NO_ZERO_DATE',''));
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> alter table test01 add birth date not null default '0000-00-00';
Query OK, 0 rows affected (0.81 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into test01
    -> values
    -> ('张国荣',1926-12-12);
ERROR 1292 (22007): Incorrect date value: '1902' for column 'birth' at row 1
mysql> insert into test01
    -> values
    -> ('张国荣','1926-12-12');
Query OK, 1 row affected (0.13 sec)

mysql> select * from test01;
+-----------+------------+
| sname     | birth      |
+-----------+------------+
| 张国荣    | 1926-12-12 |
+-----------+------------+
1 row in set (0.00 sec)

mysql> alter table test01 add sign time not null default '00:00:00' ;
Query OK, 1 row affected (0.57 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into test01
    -> values
    -> ('吕布','1111-12-11','13:35:56');
Query OK, 1 row affected (0.11 sec)

mysql> select * from test01;
+-----------+------------+----------+
| sname     | birth      | sign     |
+-----------+------------+----------+
| 张国荣    | 1926-12-12 | 00:00:00 |
| 吕布      | 1111-12-11 | 13:35:56 |
+-----------+------------+----------+
2 rows in set (0.00 sec)

mysql> create table test2(
    -> id tinyint not null default 0,
    -> ts timestamp default CURRENT_TIMESTAMP
    -> )engine myisam charset utf8;
Query OK, 0 rows affected, 1 warning (0.26 sec)

mysql> insert into test2 (id) values (1),(2);
Query OK, 2 rows affected (0.15 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test2;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  1 | 2021-04-05 17:28:02 |
|  2 | 2021-04-05 17:28:02 |
+----+---------------------+
2 rows in set (0.00 sec)

mysql> insert into test2 (id) values (3),(4);
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test2;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  1 | 2021-04-05 17:28:02 |
|  2 | 2021-04-05 17:28:02 |
|  3 | 2021-04-05 17:28:16 |
|  4 | 2021-04-05 17:28:16 |
+----+---------------------+
4 rows in set (0.00 sec)

mysql>