一、类型属性
UNSIGNED: 数字类型无符号化,没有正负号,可能会给一些计算操作带来影响, 比如两个属性相减,
ZEROFILL: 自动填充0符号位,比如1填充成0001;
SQL_MODE: 可以进行一些非法操作,建议设置严格模式;如下:
1)STRICT_TRANS_TABLES:该模式,如果一个值不能插入到一个事务表(例如innodb),则终端当前操作不影响非事务表(myisam).
2)ALLOW_INVILID_DATES:该模式不完全对日期合法性检验,只检查范围;
3)ANSI_QUOTES:启用后不能用双引号引用字符串
4)ERROR_FOR_DIVISION_BY_ZERO:该模式除0会错误,,如果未给出该模式,返回结果null;
5)higt_not_precedence_not:操作符优先顺序是表达式,比如NOT ( a between b );如果启用higt_not_precedence模式,则NOT优先级更高
6)IGNORE_SPACE:函数名和括号之间有空格
7)no_auto_create_user:禁止GRANT创建密码为空的用户;
8)NO_AUTO_VALUE_ON_ZERO:影响插入0或null代表下一个自增长值;
9)NO_BACKSLASH_ESCAPES:""作为普通字符而非转义符;
10)NO_DIR_IN_CREATE:创建表时忽视所有index directory和data directory;
11)NO_ENGINE_SUBSTITUTION:如果需要的存储引擎被禁用或者未编译,跑出错误,用默认的替代.
12)No_unsigned_subtraction:启用后,两个unsigned类型相减返回signed类型;
13)NO_ZERO_DATE:非严格模式下,可以插入0000-00-00 00:00:00的非法日期,mysql发出警告,而启用后,则不允许零日期插入,会抛出错误;
14)NO_ZERO_IN_DATE:严格模式下,不允许日期和月份为零,例如2011-00-01,mysql会直接抛出错误;
15)ONLY_FULL_GROUP_BY:对于group by的聚合操作,如果select的列不在group by中,是不合法的SQL
16)PAD_CHAR_TO_full_length:对于CHAR类型字段,不要阶段空洞数据(自动填充0x20的数据)),比如长度CHAR(10)存一个"a",显示长度是1,实际存储长度是10;
17)PIPES_AS_CONCAT: 将"||"视为字符串的连接操作符,而非或运算符,和concat函数类似;
18)REAL_AS_FLOAT:将REAL视为FLOAT的同义词而不是double的同义词;
19)STRICT_ALL_TABLES:对所有引擎的表都启用严格模式
下面是组合选项:
- ANSI: REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE
- oracle
- TRADITIONAL
- MSSQL
- DB2
- MYSQL323
- MYSQL40
- MAXDB
二、日期和时间类型
datetime 站8字节,即显示时间日期;
Date占3字节;
mysql支持例如 2011-01-01 00:01:10, 2011/01/01 00+01+1, 20110101000110, 11/01/01 00@01@10等形式,"11/"开头会被视为离最近时间进的年份;
TIMESTAMP和datetime比较
显示内容是一样的,不过timestamp占用4个字节比较小;(建议使用)
建表时 timestamp可以设置默认值,datetime列不行;
更新表时,可以设置timestamp的日期类型的列自动更新为当前时间;
YEAR:例如2020
TIME:例如12:12:12
三、日期函数
now(), current_timestamp(), sysdate()
-- current_timestamp() 和now() 相同;
-- sysdate()函数返回的是执行到当前函数的时间,而now()返回的是执行sql语句的时间
时间加减函数 date_add ,date_sub
-- 加减 (interval后面可以接负数)
select now() as 现在,
date_add(now(), interval 1 day) as 明天,
date_sub(now(), interval 1 day) as 昨天,
date_sub(now(), interval -1 day) as 明天;
-- 结果: 2020-05-30 16:05:53,2020-05-31 16:05:53,2020-05-29 16:05:53
date_format函数,格式化打印日期-->注意:不会走索引
select date_format(now(), '%Y-%m-%d %H:%m:%s') as 年月日时分秒,
date_format(now(), '%Y') as 年
;
-- 2020-05-30 16:05:50 ,2020
四、星期数的问题 ;
-- 周六 结果是5 , 返回值0-6,(0是Monday)
select weekday(now()) ;
-- 周六 结果是7,返回值1-7,(1代表Sunday)
select dayofweek(now())
-- Saturday
select dayname(now()) ;
select week(now()) ;
-- 结果 : 21 (2020-01-01为第一周, 国外周日作为一个星期的开始)
五、数字类型
整型:TINYINT(1字节)<SMALLINT(2字节)<MEDIUMINT(3字节)<INT(4字节)<BIGINT(8字节)
浮点型(非精确类型):FLOAT,DOUBLE PRECISION,
高精度类型:DECIMAL,NUMERIC
六、字符类型
因国际化需要，所有的字符存储与表示，均采用 utf 8 字符集,如果需要存储表情，那么选择 utf8mb4 来进行存储，注意它与 utf8 编码的区别;
1) CHAR和VARCHAR
CHAR定长(自动右扩充),VARCHAR变长;
2) BINARY和VARBINARY
类似上面的char和varchar,不过存储的是二进制的字符串;
3) BLOB和TEXT
BLOB是用来存储二进制大数据类型的;
BOLB和TEXT类型创建索引时,必须制定索引前缀的长度,不能有默认值;
排序时只是用前max_sort_length个字段;
INnoDB只存储前20个字节,二完整数据则放在BLOB的行溢出页中,提高查询效率;
4) ENUM和SET类型
都是集合类型,不过元素个数前者>后者;