mysql数据类型

一般来说，一个页内可以存放尽可能多的行，那么数据库的性能就越好。而且选择错误的数据类型，会加大后期数据库维护成本。

UNSIGNED属性就是将数字类型无符号化。

help int;

select f1 - f2 from t1;-- mysql对unsigned数的操作，返回都是unsigned，除非调节SQL_MODE设置

尽量不要unsigned, 除非zerofill, 若int存不了, 就用bigint

-- ---------------------------------------------

zerofill  -- 一旦启用ZEROFILL属性，mysql为列自动添加UNSIGNED属性。

zerofill只是在查询时的格式化输出（如果宽度小于设定的宽度，则自动填充0）, 数据库内部存储的还是1而不是0001

select a,HEX(a) from t\G;

int(11) 若无zerofill, 这个11毫无作用，int整形是4字节

alter talbe t1 modify f1 int(4) zerofill;

-- ---------------------------------------------

sql mode

sql mode的严格模式为MySQL提供了很好的数据校验功能, 保证了数据的准确性，尽量不要默认（空）。

show variables like ‘%sql_mode%’;  -- sql_mode有global和session会话

select @@sql_mode;

set sql_mode = ‘‘;  -- sql_mode可以设置的选项有很多，一般最好设成严格模式

set sql_mode = ‘traditional‘;

-- ---------------------------------------------

default默认值, 默认为null, 当没有给某字段显式赋值时, 就用这个值

not null即值不能为null

-- ---------------------------------------------

auto_increment

一张表中最多一个, 没有也可没有

必须是索引(key), 一般为主键索引

select last_insert_id(); -- 取自动增长列的值

重置:

alter table user auto_increment = 1;

-- 还有一些其他参数可以设置步长以及初始值

-- ---------------------------------------------

整型：tinyint 1B 、smallint 2B 、mediumint 3B 、int 4B 、bigint 8B。

位类型：BIT

关于数字的经典SQl编程问题：1、数字辅助表，2、连续范围问题。

-- ---------------------------------------------

高精度类型：

decimal 和 numeric基本相同，最大位数是65。可应用于存钱。

salary dec(5, 1)

最多5位数, 其中一定1位小数, 整数位最多4位,  5为精度, 1为标度，会4舍5入

-- ---------------------------------------------

float  -- 表达小数

where score = 3.14; -- 有问题

float(6, 2)

double precision

-- ---------------------------------------------

字符串型

国际化公司, 需要存储的字符是多种多样的, 因此要使用unicode编码的字符集如utf8

unicode是一种编码方式

mysql>show charset；

utf8是一种字符集, 是一种存储格式

utf即unicode translation format, 即把unicode编码的字符转成某种格式来存储

utf8字符集中, 一个汉字占2 ~ 4个字节, 绝大部分占3个字节, 极少数占2个或4个字节

gb2312汉字均占2个字节，gbk是gb2312的超集。

一般对于跨国性业务，最初字符集最好采用utf8，解决移植开发兼容性问题。

-- ---------------------------------------------

char

show variables like ‘%char%‘;

char(8)存‘ab‘, 占8B, insert ‘ab‘, 存储时会在ab后补6个空格, select取出时会去掉空格

8个英文或中文字符

-- ---------------------------------------------

varchar(8)存的是‘ab‘, 占3B -- 存储时需要在前缀长度列表加上实际存储的字符，占1~2字节。

8个英文或中文字符

个人简介、微博内容, 文字量不是特别大就可用varchar而不用text，有些存储引擎会将大的varchar类型字符串自动转化为text或blob类型。

select length(‘中国人‘);

一般用varchar类型因为char类型长度是固定分配的。

-- ---------------------------------------------

binary和varbinary存储二进制的字符串。binary（N）等N代表的是字节数，而char（N）等N代表的是字符长度，另外char和varchar字符比较的是本身存储的字符，忽略字符后的填充字符，而对binary和varbinary来说，按二进制值来比较。最后对于binary字符串，其填充字符是0x00，char是0x20，0x00比较的是最小字符。

-- ---------------------------------------------

text、blob, 不能有默认值，创建索引时指定前缀长度以及排序时只使用列的前max_sort_length个字节

text 存大量文字如新闻内容, 不区分大小写

blob(large binary object), 区分大小写

-- ---------------------------------------------

enum(enumeration)和set类型都是集合类型,不同的是enum最多可枚举65535个元素 适用user表

，一次只能选一个成员，而set类型最多枚举64个元素。enum结合SQL_MODE严格模式约束，有用！

性别男女, 权限有没有, 是否管理员, 是否热点新闻，

enum类型只能从成员当中选择一个，而set 类型可以选择多个，

enum用法：那么对于多个值当中选取一个的话，可以选择enum类型，比如，性别（男女）二选一

set  类型用法：比如个人爱好，可以选择多个，那么这个使用我们用set类型

sex enum(‘M‘, ‘F‘)

-- ---------------------------------------------

日期时间型：（mysql对日期和时间的设置非常宽松）

datetime  8B 、DATE 3B 、timestamp 4B 、year  1B 、 time 3B  

值用‘‘引起

datetime和timestamp显示结果一样，占字节不同，以及实际存储内容datetime可以到当前时间的毫秒数，另外在建表时timestamp类型可以设置一个默认值而datetime不行（必须对表操作，即有改动），而且timestamp会自动更新当前时间。

datetime类型表示的时间范围比timestamp的类型要大，因此，需要时间范围比较大的选择datetime类型比较合适，

timestamp类型的时间是根据时区来选择的，如果需要显示的时间与时区对应，那么选择timestamp类型。

mysql 5.6.4版本后支持秒的小数部分:type_name(fsp)。mysql类型不精确到微妙不过可以用microsecond函数提取

select now();

curdate() date(now());

curtime() time(now());

now、current_timestamp和sysdate都用于返回当前系统时间，不过前两者返回的是执行SQL语句时的时间，而sysdate函数是返回执行当前函数时的时间。

时间加减函数有date_add和date_sub。date_format函数（防止误使用）是按用户需求格式化打印日期。

日期经典sql编程问题：1、生日问题，2、重叠问题，3、星期数问题。

WEEKDAY函数返返回值是0~6,0为monday，DAYOFWEEK函数返回值是1~7，1代表sunday。

-- ----------------------------------------------------------------

字符集

字符(character)是指人类语言中最小的表义符号, 例如‘a‘、‘b‘等

给定一系列字符, 对每个字符赋予一个数值

用数值来代表对应的字符, 这一数值就是字符的编码(encoding)

例如, 我们给字符‘a‘赋予数值0, 给字符‘b‘赋予数值1

则0就是字符‘a‘的编码, 1就是字符‘b‘的编码

给定一系列字符并赋予对应的数值编码后

所有这些字符和编码对组成的集合就是字符集(character set)

例如, 给定字符集合为{‘a‘, ‘b‘}时, {‘a‘:0, ‘b‘:1}就是一个字符集

os有字符集, 软件有字符集, 若软件无字符集则使用os的字符集

校对规则(collation), 也叫字符序, 是指在同一字符集内字符之间的比较规则

确定字符序后, 才能在一个字符集上定义什么是等价的字符, 以及字符之间的大小关系

区分大小写是一种顺序, 不区分大小写是一种顺序

一个字符集可以对应多种字符序, 其中有一个是默认字符序

每个字符序唯一对应一种字符集

             _ci  不区分大小写  -- 应用程序有这需求，不过建唯一索引会出错

排序规则   ：_cs  区分大小写

             _bin 按编码值比较

对于国际化公司, 需要存储的字符是多种多样的, 因此要使用unicode编码的字符集如utf8

unicode是一种编码方式, 而utf8是一种存储方式

utf是unicode translation format, 即把unicode转成某种格式的意思

查看所有可用的字符集

show charset;

int、decimal不能指字符集

char、varchar、text字符串数据类型才可指

校对规则:  -- 没指定校对规则, 则使用该字符集默认的校对规则

show collation;

字符集与校对规则有4个级别的设置

server

db

table

column

[mysqld]

character_set_server=utf8   -- mysql5.5开始移除了default-character-set参数，取而代之为default-character-server。

==

create database ds charset=gbk;

alter database ds charset=utf8; -- 只对新创建的表有效

alter table emp charset = utf8;

客户端字符集:

default-character-set=utf8; #相当于set names utf8; --set names可以更改当前会话连接的字符集、当前会话的客户端的字符集以及当前会话返回集的字符集,mysql>status; 可看！

[mysql]

写入数据前, 查看数据前先设置客户端字符集, 即set names utf8;

本文出自 “8430296” 博客，请务必保留此出处http://8440296.blog.51cto.com/8430296/1584573