mysql支持的数据类型包括(只列举常用类型):
- 字符串类型 : CHAR,VARCHAR,BLOB,LONGBLOB,TEXT,VARCHAR(m)
- 数值类型 : INTEGER,SMALLINT,DECMAL,NUMERIC,FLOAT,REAL,DOUBLE
- 日期时间类型 : DATE,DATETIME,TIMESTAMP,TIME
字符串类型
mysql支持的字符串类型包括如图所示:
image.png
1. CHAR和VARCHAR类型区别
二者的主要区别是存储方式的不同，CHAR 列的长度固定为创建表时声明的长度，长度可以为从 0～255 的任何值；而 VARCHAR 列中的值为可变长字符串。在检索的时候，CHAR 列删除了尾部的空格，而 VARCHAR 则保留这些空格
#创建数据库表
CREATE TABLE `test1` (
`c` char(5) DEFAULT NULL,
`vc` varchar(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#插入字符串为 ab + 两个空格
insert into test1(c,vc) values('ab ','ab ');
mysql> select length(c),length(vc) from test1;
+-----------+------------+
| length(c) | length(vc) |
+-----------+------------+
| 2 | 4 |
+-----------+------------+
1 row in set (0.00 sec)
#结果char类型的会删除后面的空格
2.BINARY和VARINARY类型
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不包含非二进制字符串
3.TEXT和BOLB
当字符数据很大时VARCHAR不能满足我们的要求时，可以用TEXT和BLOB。二者之间的主要差别是 BLOB 能用来保存二进制数据，而TEXT 只能保存字符数据。
BLOB 和 TEXT 值会引起一些性能问题，特别是在执行了大量的删除操作时。删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。为了?高性能，建议定期使用 OPTIMIZE TABLE功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。
mysql> optimize table test2;
+------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------+----------+----------+-------------------------------------------------------------------+
| test.test2 | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.test2 | optimize | status | OK |
+------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.38 sec)
常用的字符串函数
image.png
数值类型
- 严格数值类型 : INTEGER,SMALINT,DECIMAL(定点数),NUMERIC
- 近似值数据类型 : FLOAT,REAL,DOUBLE,PRECISION
- 对于小数的表示有浮点数(float,double)和定点数(DECIMAL)
浮点数如果不写精度和标度，则会按照实际精度值显示，如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错；定点数如果不写精度和标度，则按照默认值 decimal(10,0)来进行操作，并且如果数据超越了精度和标度值，系统则会报错。
image.png
常见的数值函数:
image.png
日期时间类型
image.png
常用的数据类型有DATE,DATETIME,TIMESTAMP,TIME
CREATE TABLE `test2` (
`t1` date DEFAULT NULL,
`t2` datetime DEFAULT NULL,
`t3` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`t4` time DEFAULT NULL,
`t5` year(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
mysql> select * from test2;
+------------+---------------------+---------------------+----------+------+
| t1 | t2 | t3 | t4 | t5 |
+------------+---------------------+---------------------+----------+------+
| 2018-12-31 | 2018-12-31 17:18:40 | 2018-12-31 17:22:06 | 17:18:50 | 2017 |
+------------+---------------------+---------------------+----------+------+
1 row in set (0.00 sec)
常见的日期函数:
image.png
选择合适的数据类型
1.CHAR和VARCHAR怎么选择
2.xxx
CHAR和VARCHAR怎么选择
CHAR属于固定长度的字符类型，而 VARCHAR 属于可变长度的字符类型。CHAR是固定长度，所以处理起来比VARCHAR快得多，但是其缺点是浪费存储空间，程序需要对行尾空格进行处理，所以对于那些长度变化不大并且对查询速度有较高要求的数据可以考虑使用 CHAR 类型来存储。随着MYSQL的不断更新,VARCHAR数据类型的心性能也在不断的提高
使用建议:
- MyISAM 存储引擎：建议使用固定长度的数据列代替可变长度的数据列
- InnoDB 存储引擎：建议使用 VARCHAR 类型
- 在不必要的时候避免检索大型的 BLOB 或 TEXT 值
- 把 BLOB 或 TEXT 列分离到单独的表中
浮点数定点数如何选择
如何选择应考虑如下问题
- 浮点数存在误差问题
- 对货币等对精度敏感的数据，应该用定点数表示或存储
- 在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较要注意浮点数中一些特殊值的处理
TIMESTAMP 和 DATETIME如何选择
- 如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用 DATETIME，而不要使用 TIMESTAMP。因为 TIMESTAMP 表示的日期范围比 DATETIME 要短得多。
- 如果记录的日期需要让不同时区的用户使用，那么最好使用 TIMESTAMP，因为日期类型中只有它能够和实际时区相对应
参考
《深入浅出MySQL》
网友评论