Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » MySQL

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

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
2,147,483,647

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' 
select date_add('2012-03-08', interval 1 year_month) as test;

# below would translate to 2017-07-08 
select date_add('2012-03-08', interval '5-4' year_month) as test;

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



关键词:mysql 

相关文章

MySQL Cookbook for Oracle DBA
MySQL HA - Innodb Cluster
腾讯微信的PhxSQL数据库
MySQL Group Replication(MGR)
MySQL and Oracle Enterprise Manager
MySQL Tools for performance, backup, monitor
MySQL Multi-Master Single-Slave Replication
MySQL and GPL
MySQL monthly report from alibaba
MySQL备份
MySQL配置文件my.cnf
MySQL数据类型
Top
Cell186-9589-1286
QQ 375349564
E-mail ohsdba@qq.com