MySQL 数据类型
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
MariaDB [db1]> create table t1(id int)engine=innodb; # t1.frm t1.ibd MariaDB [db1]> create table t2(id int)engine=myisam; # t2.MYD t2.MYI t2.frm MariaDB [db1]> create table t3(id int)engine=memory; # t3.frm MariaDB [db1]> create table t4(id int)engine=blackhole; # t4.frm # memory和blackhole两种存储引擎只有表结构,无数据 # memory,在重启mysql或者重启机器后,表内数据清空 # blackhole,往表内插入任何数据,都相当于丢入黑洞,表内永远不存记录
存储引擎(默认InnoDB,另外了解3种:memoryDB、blackhole和Myisam)决定了表的类型
而表内存放的数据也要有不同的类型
mysql常用数据类型概览
#1. 数字: 整型:tinyinit int bigint 小数: float :在位数比较短的情况下不精准 double :在位数比较长的情况下不精准 0.000001230123123123 存成:0.000001230000 decimal:(如果用小数,则用推荐使用decimal) 精准 内部原理是以字符串形式去存 #2. 字符串: char(10):简单粗暴,浪费空间,存取速度快 root存成root000000 varchar:精准,节省空间,存取速度慢 sql优化:创建表时,定长的类型往前放,变长的往后放 比如性别 比如地址或描述信息 >255个字符,超了就把文件路径存放到数据库中。 比如图片,视频等找一个文件服务器,数据库中只存路径或url。 #3. 时间类型: 最常用:datetime
YEAR YYYY(1901/2155)
============year=========== MariaDB [db1]> create table t10(born_year year); #无论year指定何种宽度,最后都默认是year(4) MariaDB [db1]> insert into t10 values -> (1900), -> (1901), -> (2155), -> (2156); MariaDB [db1]> select * from t10; +-----------+ | born_year | +-----------+ | 0000 | | 1901 | | 2155 | | 0000 | +-----------+
DATE
YYYY-MM-DD(1000-01-01/9999-12-31)
TIME
HH:MM:SS('-838:59:59'/'838:59:59')
DATETIME
YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59 Y)
============date,time,datetime=========== MariaDB [db1]> create table t11(d date,t time,dt datetime); MariaDB [db1]> desc t11; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | d | date | YES | | NULL | | | t | time | YES | | NULL | | | dt | datetime | YES | | NULL | | +-------+----------+------+-----+---------+-------+ MariaDB [db1]> insert into t11 values(now(),now(),now()); MariaDB [db1]> select * from t11; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2017-07-25 | 16:26:54 | 2017-07-25 16:26:54 | +------------+----------+---------------------+
TIMESTAMP
YYYYMMDD HHMMSS(1970-01-01 00:00:00/2037 年某时)
============timestamp=========== MariaDB [db1]> create table t12(time timestamp); MariaDB [db1]> insert into t12 values(); MariaDB [db1]> insert into t12 values(null); MariaDB [db1]> select * from t12; +---------------------+ | time | +---------------------+ | 2017-07-25 16:29:17 | | 2017-07-25 16:30:01 | +---------------------+
============注意啦,注意啦,注意啦===========
1. 单独插入时间时,需要以字符串的形式,按照对应的格式插入
2. 插入年份时,尽量使用4位值
3. 插入两位年份时,<=69,以20开头,比如50, 结果2050
>=70,以19开头,比如71,结果1971
MariaDB [db1]> create table t12(y year);
MariaDB [db1]> insert into t12 values
-> (50),
-> (71);
MariaDB [db1]> select * from t12;
+------+
| y |
+------+
| 2050 |
| 1971 |
+------+