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', ..)                  集合
  1. 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");
    
  2. enum
    可以用 enum 尽量用 enum 类型,如性别。因为 enum 省空间

    enum 成员 (1-255)     消耗一个字节
    enum 成员 (256-65535) 消耗两个字节
    
  3. set

    set 成员 (1-8)        消耗一个字节
    set 成员 (9-16)       消耗两个字节
    set 成员 (17-24)      消耗 3 个字节
    
  4. 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);

类型的选择

  1. 尽量不要用 null,采用 not null,用其他如 -1 来表示 null
  2. IP 地址
    不要用字符串来存储,用 INT UNSIGNED

    $NUM = inet_aton(xxx.xxx.xxx.xxx)
    $STR = inet_ntoa($NUM)
    
  3. md5
    使用 unhex(md5(xxx)) 来存储 md5 的值

    select bit_length(unhex(md5('ok')));  -- 128
    select bit_length(md5('ok'));         -- 256