MySQL数据类型

MySQL表分为三类

标准表	create table table_name ( ... );
临时表	create temporary table_name ( ... );
内存表	create table table_name ( ... ) ENGINE = MEMORY;
show create table <tablename>;

MySQL数据类型分为以下类型

Character string types(字符类型)
National character string types(国家字符集)
Binary large object string types(二进制大对象类型)
Numeric types(数值类型)
Boolean types(布尔类型)
Datetime types(日期类型)
Interval types(区间型)

Enum和Set类型

Character string types

National character string types(国家字符集)

Binary large object string types(二进制大对象类型)

Numeric types(数值类型)

Boolean types(布尔类型)

布尔类型有两个选项，在MySQL中，布尔类型的名称为BOOL
Datetime types(日期类型)

支持的日期格式，也可以在后面加上微妙 .uuuuuu

YYYY-mm-dd HH:ii:ss
yy-mm-dd HH:ii:ss
yyyymmdd
yymmdd
YYYYmmddHHiiss
yymmddHHiiss
mysql> show variables like '%date%format%';

+-----------------+-------------------+
| Variable_name   | Value             |
+-----------------+-------------------+
| date_format     | %Y-%m-%d          |
| datetime_format | %Y-%m-%d %H:%i:%s |
+-----------------+-------------------+
2 rows in set (0.00 sec)

Interval types(区间型)

ENUM and SET types(枚举型和集合型)

这两种类型，有点类似外键，只能是列表中的值

CREATE TABLE Bike
(
  ID SMALLINT UNSIGNED,
  Model VARCHAR(40),
  Color ENUM('red', 'blue', 'green', 'yellow'),
  Options SET('rack', 'light', 'helmet', 'lock')
);
insert into bike values ('0001', 'chopper', 'red', 'rack,light');
insert into bike values ('0002', 'tomahawk', 'blue', 'lock,helmet,rack');
insert into bike values ('0003', 'grifter', 'green', 'light,helmet,rack');
insert into bike values ('0004', 'chopper', 'red', 'rack,light');
insert into bike values ('0005', 'grifter', 'red', 'rack,light,helmet,lock');
select id, model, color, options from bike;

CREATE TABLE allergy (symptom SET('sneezing','runny nose','stuffy head','red eyes') );
INSERT INTO allergy (symptom) VALUES('');
INSERT INTO allergy (symptom) VALUES('stuffy head');
INSERT INTO allergy (symptom) VALUES('sneezing,red eyes');
INSERT INTO allergy (symptom) VALUES('sneezing,stuffy head,red eyes');
select symptom,symptom+0 'Index position' from allergy;

auto_increment
auto_increment属性用于作为主键的列，并且每个表只允许有一个auto_increment列
id int not null auto_increment primary key

binary
binary只用于char和varchar值，当为列指定了该属性时，将以区分大小写的方式排序。如果不适用该属性，将不区分大小写
name char(25) binary not null

default
该属性确保在没有任何值可用的情况下，赋予某个常量值，这个值必须是常量，此属性无法用于BLOB或TEXT列

not null
如果列定义为not null，将不允许向该列插入null值，在重要情况下建议使用not null属性

null
为列指定null属性时，该列可以保持为空，准确的说null是'无'，而不是空字符串或0

primary key
primary key用于确保指定行的唯一性，指定为主键的列，其值不能重复，也不能为空。

unique
unique属性的列将确保所有值都有不同的值，除了NULL外，和Oracle是一样的

zerofill
zerofill可用于任何数值类型，用0填充所有剩余字段空间
id int unsigned zerofill not null

sql_mode参数(她控制着数据的校验和SQL语法的查)

ALLOW_INVALID_DATES - any date and time is allowed
ERROR_FOR_DIVISION_BY_ZERO - division or modulo 0 functions return NULL with no warnings
NO_AUTO_CREATE_USER - a GRANT statement only creates a new user automatically if a non-empty password is specified
NO_AUTO_VALUE_ON_ZERO - a numeric data type with the AUTO_INCREMENT property will issue the next number in the sequence if 0 or a NULL is inserted
NO_ENGINE_SUBSTITION - an ALTER TABLE or CREATE TABLE that specifies a disabled or unavailable storage engine throws an error
NO_ZERO_DATE - a warning is generated if a date field has a zero date inserted or updated
NO_ZERO_IN_DATE - partial or total zero dates are allowed
STRICT_ALL_TABLES - invalid data values are reject in all tables, an error is thrown
STRICT_TRANS_TABLES - invalid data values are rejected in transactional tables only, an error is thrown 

HIGH_NOT_PROCEDENCE - the NOT operator has a higher precedence
IGNORE_SPACE - allows a space between a built-in function and the open parenthesis
NO_BACKSLASH_ESCAPES - the backslash becomes a ordinary character
NO_DIR_IN_CREATE - ignores options DATA DICTIONARY and INDEX DICTIONARY when using the CREATE TABLE statement
PAD_CHAR_TO_FULL_LENGTH - the behavior of retrieving a fixed-width string from a table is changed 

ANSI QUOTES - a double quote is now treated as an identifier quote
NO_FIELD_OPTIONS - the output of SHOW CREATE TABLE will not display any field definition options and properties
NO_KEY_OPTIONS - the output of SHOW CREATE TABLE will not display any index definition options and properties
NO_TABLE_OPTIONS - the output of SHOW CREATE TABLE will not display any table definition options and properties
NO_UNSIGNED_SUBSTRACTION - the result of a subtraction is always a signed numeric value
ONLY_FULL_GROUP_BY - a GROUP BY query requires the fields in the SELECT and HAVING clauses to contain only aggregated fields and the fields in the GROUP BY clause
PIPES_AS_CONCAT - || is set as an alias for OR
REAL_AS_FLOAT - REAL is an alias for DOUBLE 

ANSI
DB2
MAXDB
MSSQL
MYSQL323
MYSQL40
ORACLE
POSTGRESQL
TRADITIONAL

mysql> show variables like 'sql_mode'\G

*************************** 1. row ***************************
Variable_name: sql_mode
        Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

mysql> 

查看、设置sql_mode属性 

show global variables like 'sql_mode';
show session variables like 'sql_mode'; 

SET GLOBAL sql_mode=<value>,<value>,<value>.... 
SET SESSION sql_mode=<value>,<value>,<value>.... 

Reference

http://dev.mysql.com/doc/refman/5.7/en/data-types.html