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
String Type | Character string type |
Size
|
Example |
Fixed-width strings | character(length) |
|
create table string_type ( fixwidth1 CHARACTER(10) NOT NULL DEFAULT '', fixwidth2 CHAR(10) NOT NULL DEFAULT'', fixwidth3 CHAR(10) CHARACTER SET utf16 COLLATE utf16_general_ci, varilength1 CHARACTER VARYING(10) NOT NULL DEFAULT '', varilength2 CHAR VARYING(10) NOT NULL DEFAULT '', varilength3 VARCHAR(10) NUL NULL DEFAULT '' ); |
char(length) |
0-255
|
||
variable-length strings | character varying(length) |
|
|
char varying(length) |
|
||
varchar(length) |
0-65,535
|
||
tinytext |
0-255
|
||
text |
0-64Kb
|
||
meduimtext |
0-16Mb
|
||
longtext |
0-4Gb
|
||
Character objects | character largeobject |
|
|
char large object |
|
||
CLOB |
|
National character string types(国家字符集)
String Type | Character string type |
Size
|
Example |
Fixed-width strings | national character(length) |
|
create table string_type ( fixwidth1 NATIONAL CHARACTER(10) NOT NULL DEFAULT '', fixwidth2 NCHAR(10) NOT NULL DEFAULT'', varilength1 NCHAR(10) NOT NULL DEFAULT '', ); |
national char(length) | |||
nchar(length) |
0-255
|
||
variable-length strings | national character varying(length) |
|
|
national char varying(length) |
|
||
nchar(length) |
0-65,535
|
||
Character objects | national character largeobject |
|
|
nchar large object |
|
||
NCLOB |
|
Binary large object string types(二进制大对象类型)
Binary type |
Size
|
Example |
binary | 0-255 |
create table string_type ( bin1 BINARY(100) NOT NULL DEFAULT '', blob1 BLOB(100) NOT NULL DEFAULT'', ); |
varbinary | 0-65,532 | |
tinyblob | 0-255 | |
blob | 0-65,532 | |
meduimblob | 0-16Mb | |
longblob | 0-4Gb |
Numeric types(数值类型)
Numeric Type | Type | signed range | unsigned range |
Size (bytes)
|
Example |
Exact | numeric(g,f) |
maximum limits depend on the hardware and O/S max for g = 65, max for f = 30 |
depends
|
create table num_test ( bt1 BIT(10), fl1 FLOAT(10), dl DECIMAL(5,4), uty1 TINYINT(10) unsigned NOT NULL AUTO_INCREMENT ); |
|
decimal(g,f) |
maximum limits depend on the hardware and O/S max for g = 65, max for f = 30 |
depends
|
|||
smallint | -32,768 to 32,767 | 0 to 65,535 |
2
|
||
integer |
-2,147,483,648 to |
0 to 4,294,967,295 |
4
|
||
bigint | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 | 0 to 18,446,744,073,709,551,615 |
8
|
||
tinyint | -128 to 127 | 0 to 255 |
1
|
||
meduimint | -8,388,608 to 8,388,607 | 0 to 16,777,215 |
3
|
||
bit(x) |
1 to 64
|
1-64
|
|||
serial |
alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE KEY
|
n/a
|
|||
approximate | float(p) |
maximum limits depend on the hardware and O/S max for p = 24 |
4
|
||
real |
alias for DOUBLE or FLOAT
|
n/a
|
|||
double(g,f) |
maximum limits depend on the hardware and O/S max for g = 53 max for f = 30 |
8
|
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)mysql>
Datetime type | Range |
Size (bytes)
|
Zero value |
date | '1000-01-01' to '9999-12-31' |
3
|
'0000-00-00' |
datetime | '1000-01-01 00:00:00' to '9999-12-31 23:59:59' |
8
|
'0000-00-00 00:00:00' |
timestamp | '1970-01-01 00:00:00' to '2038-01-18 22:14:07' |
4
|
'0000-00-00 00:00:00' |
time | '-838:59:59' to '838:59:59' |
3
|
'00:00:00' |
year(2) | 00 to 99 |
1
|
'00' |
year(4) | 1901 to 2155 |
1
|
'0000' |
Interval types(区间型)
Interval expressions |
# below would translate to '2012-04-08'
# below would translate to 2017-07-08 |
||
Interval data types
|
|||
Name |
Format
|
Example | |
microsecond |
n
|
INTERVAL 5 MICROSECOND | |
second |
n
|
INTERVAL 5 SECOND | |
minute |
n
|
INTERVAL 5 MINUTE | |
hour |
n
|
INTERVAL 5 HOUR | |
day |
n
|
INTERVAL 5 DAY | |
week |
n
|
INTERVAL 5 WEEK | |
month |
n
|
INTERVAL 5 MONTH | |
quarter |
n
|
INTERVAL 5 QUARTER | |
year |
n
|
INTERVAL 5 YEAR | |
second_microsecond |
'n.n'
|
INTERVAL '5.4' SECOND_MICROSECOND | |
minute_microsecond |
'n.n'
|
INTERVAL '5.4' MINUTE_MICROSECOND | |
minute_second |
'n:n'
|
INTERVAL '5:4' MINUTE_SECOND | |
hour_microsecond |
'n.n'
|
INTERVAL '5.4' HOUR_MICROSECOND | |
hour_second |
'n:n:n'
|
INTERVAL '5:4:3' HOUR_SECOND | |
hour_minute |
'n:n'
|
INTERVAL '5:4' HOUR_MINUTE | |
day_microsecond |
'n.n'
|
INTERVAL '5.4' DAY_MICROSECOND | |
day_second |
'n n:n:n'
|
INTERVAL '5 4:3:2' DAY_SECOND | |
day_minute |
'n n:n'
|
INTERVAL '5 4:3' DAY_MINUTE | |
day_hour |
'n n'
|
INTERVAL '5 4' DAY_HOUR | |
year_month |
'n-n'
|
INTERVAL '5-4' YEAR_MONTH |
ENUM and SET types(枚举型和集合型)
ENUM | this is a enumerated list of 1 to 65,535 strings which indicate the allowed values for the field, only one of the values can be stored in the list |
SET | this is a enumerated list of 1 to 64 strings which indicate the allowed values for the field, any combination of the strings in the enumerated list can be stored as a comma-delimited list. |
这两种类型,有点类似外键,只能是列表中的值
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;数据类型可用的属性
Data Type | Attributes that can be used |
Character Strings | NOT NULL, NULL, DEFAULT and BINARY |
National Character Strings | NOT NULL, NULL, DEFAULT and BINARY |
Binary | NOT NULL, NULL and BINARY (only binary can use this) |
Numeric data | NOT NULL, NULL, DEFAULT, AUTO_INCREMENT, SIGNED, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE |
DateTime | NOT NULL, NULL and DEFAULT |
Enum and Sets | NOT NULL, NULL and DEFAULT |
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数据类型的简单描述
数据类型
含义
char(n)
固定长度,最多255个字符
varchar(n)
可变长度,最多65535个字符
tinytext
可变长度,最多255个字符
text
可变长度,最多65535个字符
mediumtext
可变长度,最多2的24次方-1个字符
date
3字节,日期,格式:1977-01-01
time
3字节,时间,格式:01:00:00
datetime
8字节,日期时间,格式:1977-01-01 01:00:00
timestamp
4字节,自动存储记录修改的时间
year
1字节,年份
tinyint
1字节,范围(-128~127)
smallint
2字节,范围(-32768~32767)
mediumint
3字节,范围(-8388608~8388607)
int
4字节,范围(-2147483648~2147483647)
bigint
8字节,范围(+-9.22*10的18次方)
float(m,
d)
4字节,单精度浮点型,m总个数,d小数位
double(m,
d)
8字节,双精度浮点型,m总个数,d小数位
decimal(m,
d)
decimal是存储为字符串的浮点数
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