基础教程 - MySQL数据类型 - 《MySQL操作手册》

char、varchar的区别
datetime、timestamp的区别
int(4)、int(10)的区别
enum的使用

char、varchar的区别

mysql> create database gaokao;
Query OK, 1 row affected (0.00 sec)
mysql> show create database gaokao\G
*************************** 1. row ***************************
       Database: gaokao
Create Database: CREATE DATABASE `gaokao` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
mysql> create table yuwen(name varchar(20),mobil char(11));
Query OK, 0 rows affected (0.04 sec)
mysql> show create table yuwen\G
*************************** 1. row ***************************
       Table: yuwen
Create Table: CREATE TABLE `yuwen` (
  `name` varchar(20) DEFAULT NULL,
  `mobil` char(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> insert into yuwen values('aa','13887651111'),(' bb',' 1233331234'),('cc ','12345678900 ');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from yuwen;
+------+-------------+
| name | mobil       |
+------+-------------+
| aa   | 13887651111 |
|  bb  |  1233331234 |
| cc   | 12345678900 |
+------+-------------+
3 rows in set (0.00 sec)
###错误的示例
mysql> select concat(''',name,''') from yuwen;
+----------------------+
| concat(''',name,''') |
+----------------------+
| ',name,'             |
| ',name,'             |
| ',name,'             |
+----------------------+
3 rows in set (0.00 sec)
mysql> select concat("'",name,"'") from yuwen;
+----------------------+
| concat("'",name,"'") |
+----------------------+
| 'aa'                 |
| ' bb'                |
| 'cc '                |
+----------------------+
3 rows in set (0.00 sec)
mysql> select concat("'",name,"'"),concat("'",mobil,"'") from yuwen;
+----------------------+-----------------------+
| concat("'",name,"'") | concat("'",mobil,"'") |
+----------------------+-----------------------+
| 'aa'                 | '13887651111'         |
| ' bb'                | ' 1233331234'         |
| 'cc '                | '12345678900'         |
+----------------------+-----------------------+
3 rows in set (0.00 sec)
mysql> help concat;
Name: 'CONCAT'
Description:
Syntax:
CONCAT(str1,str2,...)
Returns the string that results from concatenating the arguments. May
have one or more arguments. If all arguments are nonbinary strings, the
result is a nonbinary string. If the arguments include any binary
strings, the result is a binary string. A numeric argument is converted
to its equivalent nonbinary string form.
CONCAT() returns NULL if any argument is NULL.
URL: http://dev.mysql.com/doc/refman/5.7/en/string-functions.html
Examples:
mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'
mysql> select * from yuwen;
+------+-------------+
| name | mobil       |
+------+-------------+
| aa   | 13887651111 |
|  bb  |  1233331234 |
| cc   | 12345678900 |
+------+-------------+
3 rows in set (0.00 sec)

char固定长度的字符串
    特点
        1、最大长度：255
      2、会自动删除末尾的空格
      3、检索效率、写效率 会比varchar高，以空间换时间
    应用场景：  
      1、存储长度波动不大的数据，如：md5摘要
      2、存储短字符串、经常更新的字符串

varchar根据实际内容长度保存数据
     特点
         1、使用最小的符合需求的长度。
    2、varchar(n) n小于等于255使用额外一个字节保存长度，n>255使用额外两个字节保存长度。
    3、varchar(5)与varchar(255)保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小
    4、varchar在mysql5.6之前变更长度，或者从255一下变更到255以上时，都会导致锁表。
  应用场景
      1、存储长度波动较大的数据，如：文章，有的会很短有的会很长
    2、字符串很少更新的场景，每次更新后都会重算并使用额外存储空间保存长度
    3、适合保存多字节字符，如：汉字，特殊字符等

datetime、timestamp的区别

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-06-07 09:48:11 |
+---------------------+
1 row in set (0.00 sec)

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2021 |
+-------------+
1 row in set (0.00 sec)

mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
|            6 |
+--------------+
1 row in set (0.00 sec)

mysql> select day(now());
+------------+
| day(now()) |
+------------+
|          7 |
+------------+
1 row in set (0.00 sec)

mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
|           9 |
+-------------+
1 row in set (0.00 sec)

mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
|            49 |
+---------------+
1 row in set (0.00 sec)

mysql> show create table yuwen\G
*************************** 1. row ***************************
       Table: yuwen
Create Table: CREATE TABLE `yuwen` (
  `name` varchar(20) DEFAULT NULL,
  `mobil` char(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> alter table yuwen add exam_time datetime;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table yuwen\G
*************************** 1. row ***************************
       Table: yuwen
Create Table: CREATE TABLE `yuwen` (
  `name` varchar(20) DEFAULT NULL,
  `mobil` char(11) DEFAULT NULL,
  `exam_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from yuwen;
+------+-------------+-----------+
| name | mobil       | exam_time |
+------+-------------+-----------+
| aa   | 13887651111 | NULL      |
|  bb  |  1233331234 | NULL      |
| cc   | 12345678900 | NULL      |
+------+-------------+-----------+
3 rows in set (0.00 sec)

mysql> insert into yuwen values('dd','12345678901',now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from yuwen;
+------+-------------+---------------------+
| name | mobil       | exam_time           |
+------+-------------+---------------------+
| aa   | 13887651111 | NULL                |
|  bb  |  1233331234 | NULL                |
| cc   | 12345678900 | NULL                |
| dd   | 12345678901 | 2021-06-07 09:52:16 |
+------+-------------+---------------------+
4 rows in set (0.00 sec)

mysql> update yuwen set mobil = '13678900987' where name = 'dd';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from yuwen;
+------+-------------+---------------------+
| name | mobil       | exam_time           |
+------+-------------+---------------------+
| aa   | 13887651111 | NULL                |
|  bb  |  1233331234 | NULL                |
| cc   | 12345678900 | NULL                |
| dd   | 13678900987 | 2021-06-07 09:52:16 |
+------+-------------+---------------------+
4 rows in set (0.00 sec)


mysql> alter table yuwen add modify_time timestamp;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table yuwen\G
*************************** 1. row ***************************
       Table: yuwen
Create Table: CREATE TABLE `yuwen` (
  `name` varchar(20) DEFAULT NULL,
  `mobil` char(11) DEFAULT NULL,
  `exam_time` datetime DEFAULT NULL,
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from yuwen;
+------+-------------+---------------------+---------------------+
| name | mobil       | exam_time           | modify_time         |
+------+-------------+---------------------+---------------------+
| aa   | 13887651111 | NULL                | 2021-06-07 09:54:48 |
|  bb  |  1233331234 | NULL                | 2021-06-07 09:54:48 |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 09:54:48 |
+------+-------------+---------------------+---------------------+
4 rows in set (0.00 sec)


mysql> update yuwen set mobil = '12333312349' where name = ' bb';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from yuwen;
+------+-------------+---------------------+---------------------+
| name | mobil       | exam_time           | modify_time         |
+------+-------------+---------------------+---------------------+
| aa   | 13887651111 | NULL                | 2021-06-07 09:54:48 |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 09:54:48 |
+------+-------------+---------------------+---------------------+
4 rows in set (0.00 sec)


------------------------------------------------------
datetime
    占用8个字节
  与时区无关，数据库底层时区配置，对datetime无效
  精确到毫秒
  可保存时间范围大
  不要使用字符串存储日期类型，占用空间大，损失日期类型函数的便捷性
timestamp
    占用4个字节
  时间范围：1970-01-01到2038-01-19
  精确到秒
    采用整形存储
  依赖数据库设置的时区
  自动更新timestamp列的值
date
    占用的字节数比使用字符串、datetime、int存储要少，使用date类型只需要3个字节
  使用date类型还可以利用日期时间函数进行日期之间的计算
  date类型用于保存1000-01-01到9999-12-31之间的日期

int(4)、int(10)的区别

对数据的存储范围没有任何区别，配合zerofill可以看出不足int(x) x宽度的时候会补0

mysql> create table iwidth(aid int(4) zerofill,bid int(10) zerofill);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into iwidth select 1,2;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into iwidth select 1234,123456789;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into iwidth select 1234,1234567890;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> select * from iwidth;
+------+------------+
| aid  | bid        |
+------+------------+
| 0001 | 0000000002 |
| 1234 | 0123456789 |
| 1234 | 1234567890 |
+------+------------+
3 rows in set (0.00 sec)

mysql> insert into iwidth select 1234567890,1234567890;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from iwidth;
+------------+------------+
| aid        | bid        |
+------------+------------+
|       0001 | 0000000002 |
|       1234 | 0123456789 |
|       1234 | 1234567890 |
| 1234567890 | 1234567890 |
+------------+------------+
4 rows in set (0.00 sec)

mysql>

enum的使用

mysql> alter table yuwen add sex char(1);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update yuwen set sex = '男' where name = 'aa';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from yuwen;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 | NULL |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 | NULL |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 09:54:48 | NULL |
+------+-------------+---------------------+---------------------+------+
4 rows in set (0.00 sec)

mysql> update yuwen set sex = '妖' where name = 'dd';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * FROM YUWEN;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 | NULL |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 | NULL |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 10:00:36 | 妖   |
+------+-------------+---------------------+---------------------+------+
4 rows in set (0.00 sec)

mysql> alter table yuwen modify sex enum('男','女','未知');
ERROR 1265 (01000): Data truncated for column 'sex' at row 4


mysql> select * FROM YUWEN;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 | NULL |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 | NULL |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 10:00:36 | 妖   |
+------+-------------+---------------------+---------------------+------+
4 rows in set (0.00 sec)

mysql> help change;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   ALTER TABLE
   CHANGE MASTER TO
   CHANGE REPLICATION FILTER

mysql> help alter;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   ALTER DATABASE
   ALTER EVENT
   ALTER FUNCTION
   ALTER INSTANCE
   ALTER LOGFILE GROUP
   ALTER PROCEDURE
   ALTER SERVER
   ALTER TABLE
   ALTER TABLESPACE
   ALTER USER
   ALTER VIEW
   GRANT
   SPATIAL




mysql> alter table yuwen change sex sex enum('男','女','未知');
ERROR 1265 (01000): Data truncated for column 'sex' at row 4

mysql> select * from yuwen;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 | NULL |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 | NULL |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 10:00:36 | 妖   |
+------+-------------+---------------------+---------------------+------+
4 rows in set (0.00 sec)



mysql> update yuwen set sex  = '女' where name = 'dd';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> alter table yuwen change sex sex enum('男','女','未知');
Query OK, 4 rows affected (0.12 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from yuwen;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 | NULL |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 | NULL |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 10:07:12 | 女   |
+------+-------------+---------------------+---------------------+------+
4 rows in set (0.00 sec)

mysql> show create table yuwen\G
*************************** 1. row ***************************
       Table: yuwen
Create Table: CREATE TABLE `yuwen` (
  `name` varchar(20) DEFAULT NULL,
  `mobil` char(11) DEFAULT NULL,
  `exam_time` datetime DEFAULT NULL,
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `sex` enum('男','女','未知') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into yuwen(name,mobil,exam_time,sex) values('ee','13678900987',now(),'男');
Query OK, 1 row affected (0.01 sec)

mysql> select * from yuwen;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 | NULL |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 | NULL |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 10:07:12 | 女   |
| ee   | 13678900987 | 2021-06-07 10:09:29 | 2021-06-07 10:09:29 | 男   |
+------+-------------+---------------------+---------------------+------+
5 rows in set (0.00 sec)

mysql> insert into yuwen(name,mobil,exam_time,sex) values('ff','13678900997',now(),'妖');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1


mysql> insert into yuwen(name,mobil,exam_time) values('ff','13678900997',now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from yuwen;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 | NULL |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 | NULL |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 10:07:12 | 女   |
| ee   | 13678900987 | 2021-06-07 10:09:29 | 2021-06-07 10:09:29 | 男   |
| ff   | 13678900997 | 2021-06-07 10:11:02 | 2021-06-07 10:11:02 | NULL |
+------+-------------+---------------------+---------------------+------+
6 rows in set (0.00 sec)

mysql> select sex from yuwen;
+------+
| sex  |
+------+
| 男   |
| NULL |
| NULL |
| 女   |
| 男   |
| NULL |
+------+
6 rows in set (0.00 sec)

mysql> select sex+0 from yuwen;
+-------+
| sex+0 |
+-------+
|     1 |
|  NULL |
|  NULL |
|     2 |
|     1 |
|  NULL |
+-------+
6 rows in set (0.00 sec)

mysql> insert into yuwen(name,mobil,exam_time,sex) values('gg','13678900997',now(),'未知');
Query OK, 1 row affected (0.01 sec)

mysql> select sex from yuwen;
+------+
| sex  |
+------+
| 男   |
| NULL |
| NULL |
| 女   |
| 男   |
| NULL |
| 未知 |
+------+
7 rows in set (0.00 sec)

mysql> select sex+0 from yuwen;
+-------+
| sex+0 |
+-------+
|     1 |
|  NULL |
|  NULL |
|     2 |
|     1 |
|  NULL |
|     3 |
+-------+
7 rows in set (0.00 sec)

mysql> show create table yuwen\G
*************************** 1. row ***************************
       Table: yuwen
Create Table: CREATE TABLE `yuwen` (
  `name` varchar(20) DEFAULT NULL,
  `mobil` char(11) DEFAULT NULL,
  `exam_time` datetime DEFAULT NULL,
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `sex` enum('男','女','未知') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from yuwen where sex = 1;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
| ee   | 13678900987 | 2021-06-07 10:09:29 | 2021-06-07 10:09:29 | 男   |
+------+-------------+---------------------+---------------------+------+
2 rows in set (0.00 sec)

mysql> select * from yuwen where sex = 3;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| gg   | 13678900997 | 2021-06-07 10:12:32 | 2021-06-07 10:12:32 | 未知 |
+------+-------------+---------------------+---------------------+------+
1 row in set (0.00 sec)

mysql> insert into yuwen(name,mobil,exam_time,sex) values('hh','13678900997',now(),3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from yuwen;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 | NULL |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 | NULL |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 10:07:12 | 女   |
| ee   | 13678900987 | 2021-06-07 10:09:29 | 2021-06-07 10:09:29 | 男   |
| ff   | 13678900997 | 2021-06-07 10:11:02 | 2021-06-07 10:11:02 | NULL |
| gg   | 13678900997 | 2021-06-07 10:12:32 | 2021-06-07 10:12:32 | 未知 |
| hh   | 13678900997 | 2021-06-07 10:13:41 | 2021-06-07 10:13:41 | 未知 |
+------+-------------+---------------------+---------------------+------+
8 rows in set (0.00 sec)

mysql>