首页
mysql 的数据类型和常用函数
21 June 2016
数字
tinyint 1 smallint 2 mediumint 3 int 4 bigint 8 id int(5) zero fill; -- 表示的是 当插入 1 的时候，筛选出来的是 00001
浮点
float 4 double 8 float (m, d) -- m 是总位数，d 是小数点后面的位数 double (m, d) -- m 是总位数，d 是小数点后面的位数
定点
decimal(m, d) -- m 是总位数, d 是小数点后面的位数 m 默认是 10，d 默认是 2 m 最大是 64，d 最大是 30 最大 65 个 9 最小 65 个 -9
字符串
char(n) 最大 255
varchar(n) 最大 65535
tinytext, text, mediumtext, longtext 不用指定长度
binary, varbinary, blob 二进制
enum ('v1', 'v2', ..) 枚举
set ('v1', 'v2', ..) 集合
char 和 varchar 的区别
char 不保留数据最后的空格，varchar 保留
drop table if exists test.test; create table test.test ( id int unsigned not null primary key auto_increment, one varchar(3) not null default '', two char(3) not null default '' ) engine = innodb default charset = gbk; insert into test(one, two) value ("abc", "abc");
enum
可以用 enum 尽量用 enum 类型，如性别。因为 enum 省空间
enum 成员 (1-255) 消耗一个字节 enum 成员 (256-65535) 消耗两个字节
set
set 成员 (1-8) 消耗一个字节 set 成员 (9-16) 消耗两个字节 set 成员 (17-24) 消耗 3 个字节
- text 和 varchar
text 和 varchar 经常更新会造成文件空洞
optimize table xxx;回收文件空洞
日期
date YYYY-MM-DD 4 datetime YYYY-MM-DD HH:MI:SS 8 timestamp YYYY-MM-DD HH:MI:SS 4 <== 最大支持到 2038 年 year YYYY 1 time HHH:MI:SS 3
mysql> set time_zone = '+8:00'; flush privileges;
➜ my.cnf default-time_zone = '+8:00'
mysql> select now(); | 2016-06-23 13:27:54 | mysql> select now() + interval 5 hour; | 2016-06-23 18:28:10 |
json
use test; create table facts (sentence json); insert into facts values (json_object("mascot", "our mascot is a dolphin named \"sakila\".")); insert into facts values ('{"mascot": "our mascot is a dolphin named \\"sakila\\"."}');
-> ->>
mysql> select sentence->"$.mascot" from facts;
"Our mascot is a dolphin named \"Sakila\"."
mysql> select sentence->>"$.mascot" from facts;
Our mascot is a dolphin named "Sakila".
mysql> select json_object('key1', 1, 'key2', 'abc', 'key1', 'def');
{"key1": 1, "key2": "abc"}
json_merge
mysql> select json_merge('[1, 2]', '["a", "b"]', '[true, false]');
[1, 2, "a", "b", true, false]
mysql> select json_merge('{"a": 1, "b": 2}', '{"c": 3, "a": 4}');
{"a": [1, 4], "b": 2, "c": 3}
mysql> select json_merge('1', '2');
[1, 2]
mysql> select json_merge('[10, 20]', '{"a": "x", "b": "y"}');
[10, 20, {"a": "x", "b": "y"}]
json_extract
mysql> select json_extract('{"id": 14, "name": "Aztalan"}', '$.name');
"Aztalan"
mysql> select json_extract('[3, {"a": [5, 6], "b": 10}, [99, 100]]', '$[0]');
3
mysql> select json_extract('[3, {"a": [5, 6], "b": 10}, [99, 100]]', '$[1].a');
[5, 6]
mysql> select json_extract('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');
[1, 2, [3, 4, 5]]
mysql> select json_extract('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c');
[3, 4, 5]
mysql> select json_extract('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]');
[3, 4, 5]
mysql> select json_extract('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
[1, 2]
json_set，可以 set 已经存在的 json path，也可以 set 不存在的 json path
mysql> select json_set('["a", {"b": [true, false]}, [10, 20]]', '$[1].b[0]', 1, '$[2][2]', 2);
["a", {"b": [1, false]}, [10, 20, 2]]
json_insert，可以 insert 已经存在的 json path，不存在的 json path 被忽略掉
mysql> select json_insert('["a", {"b": [true, false]}, [10, 20]]', '$[1].b[0]', 1, '$[2][2]', 2);
["a", {"b": [true, false]}, [10, 20, 2]]
json_replace，可以 replace 已经存在的 json path，不存在的 json path 被忽略掉
mysql> select json_replace('["a", {"b": [true, false]}, [10, 20]]', '$[1].b[0]', 1, '$[2][2]', 2);
["a", {"b": [1, false]}, [10, 20]]
json_remove
mysql> select json_remove('["a", {"b": [true, false]}, [10, 20]]', '$[2]', '$[1].b[1]', '$[1].b[1]');
["a", {"b": ["true"]}]
mysql> select json_remove('["a", {"b": [true, false]}, [10, 20]]', '$[2]', '$[1].b[0]', '$[1].b[0]');
["a", {"b": ["false"]}]
mysql> select json_remove('["a", {"b": [true, false]}, [10, 20]]', '$[2]', '$[1].b[0]', '$[1].b[0]');
["a", {"b": []}]
函数
数学函数
abs(x) 返回 x 的绝对值 bin(x) 返回 x 的二进制 (oct 返回八进制, hex 返回十六进制) ceil(x) 返回大于 x 的最小整数值 floor(x) 返回小于 x 的最大整数值 pi() 返回 pi 的值（圆周率） rand() 返回 0 到 1 内的随机值 (可以通过 rand() * 10 进行控制范围) sqrt(x) 返回一个数的平方根 round(x, y) 返回参数 x 的四舍五入的有 y 位小数的值 truncate(x, y) 返回数字 x 截短为 y 位小数的结果 format(x, y) 把 x 格式化为以逗号隔开的数字序列，y 是结果的小数位数
聚合函数
avg(col) 返回指定列的平均值 count(col) 返回指定列中非 null 值的个数 count distinct(col) 返回指定列中非 null 值且非重复的个数 min(col) 返回指定列的最小值 max(col) 返回指定列的最大值 sum(col) 返回指定列的所有值之和 group_concat(col) 返回由 col 那一列连接组合而成的结果 bit_and(col) 对结果进行 bit_and bit_or(col) 对结果进行 bit_or bit_xor(col) 对结果进行 bit_xor
字符串函数
ascii(char) 返回字符的 ascii 码值
bit_length(str) 返回字符串的比特长度
bit_count(xxx) 计算 xxx 的二进制有多少个一
length(str) 返回字符串 str 中的字符数
concat(s1, s2) 将 s1, s2 连接成字符串
concat_ws(sep, s1, s2) 将 s1, s2 连接成字符串, 并用 sep 字符间隔
left(str, x) 返回字符串 str 中最左边的 x 个字符
right(str, x) 返回字符串 str 中最右边的 x 个字符
ltrim(str) 从字符串 str 中切掉开头的空格
rtrim(str) 返回字符串 str 尾部的空格
trim(str) 去除字符串首部和尾部的所有空格
rpad ('abc', 4, '0') abc0
rpad ('abc', 6, '0') abc000
lpad ('abc', 4, '0') 0abc
lpad ('abc', 6, '0') 000abc
日期函数
curdate() curtime() now() unix_timestamp([xxx]) from_unixtime(xxx) date(xxx) time(xxx) week(xxx) year(xxx) month(xxx) day(xxx) hour(xxx) minute(xxx) second(xxx)
系统信息函数
database() 返回当前数据库名 benchmark(count, expr) 将表达式 expr 重复运行 count 次 connection_id() 返回当前客户的连接 id found_rows() 返回最后一个 select 查询进行检索的总行数 user() 返回当前登陆用户名 version() 返回 mysql 服务器的版本
加密函数
md5(xxx) sha1(xxx) sha2(xxx, 256) password(str)
其他函数
inet_aton(ip) 返回 ip 地址的数字表示
inet_ntoa(num) 返回数字所代表的 ip 地址
select inet_aton('10.122.89.47');
select inet_ntoa(175790383);
类型的选择
- 尽量不要用 null，采用 not null，用其他如 -1 来表示 null
IP 地址
不要用字符串来存储，用INT UNSIGNED
$NUM = inet_aton(xxx.xxx.xxx.xxx) $STR = inet_ntoa($NUM)
md5
使用 unhex(md5(xxx)) 来存储 md5 的值
select bit_length(unhex(md5('ok'))); -- 128 select bit_length(md5('ok')); -- 256
