#11 MySQL Boolean

2017-09-01

我们通常使用以下几种方式表示布尔值:

  1. tinyint(1)
  2. bit(1)
  3. enum('Y','N')

在 MySQL 中 bool, booleantinyint(1) 的别名。

如果只是一个 True OR False 的布尔型,没有比 bit(1) 更合适的了。
但是也有些时候,我们有好几个 bool 型用一个字段表示,最好用 bit(m),我也用过 int 型。

附:不同值的长度

Type Bit Byte Note
tinyint 8 1  
smallint 16 2  
middleint 24 3  
int 32 4  
bigint 64 8  
bit(m) m (m+7)/8  
binary[(m)]   m m 默认值:1
varbinary(m)   (m+1)  
tinyblob   (L+1) 1B 长度,最长 255B
blob[(m)]   (L+2) 2B 长度,最长 64KB - 1
mediumblob   (L+3) 3B 长度,最长 16MB - 1
longblob   (L+4) 4B 长度,最长 4GB - 1
enum('a',..)   1/2 最多可以存储 2^16 - 1 个值
set('a',..)   1/2/3/4/8 最多可以存储 64 个值

PS: blob 如果指定长度 m,MySQL 会选择足够存储数据的最小长度。
PS: MySQL set 类型

参考资料与拓展阅读

#10 数据库:应该怎么存储 IP 地址

2017-08-25

IP 地址的存储方式就两种:字符串,整型数。

表示方法

ipv4 VARCHAR(15) -- xx.xx.xx.xx
ipv6 VARCHAR(39) -- xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx

ipv4 INT UNSIGNED
ipv6 BINARY(16)

-- 如果同时需要表达 IPv4 和 IPv6
ip VARCHAR(39)
ip BINARY(16)

PS:我还在有些地方看到过这种采用 12 位整数表示 IPv4 的方法:'192.168.0.1' => 192168000001, 如果存到 MySQL 的话,只能采用 BIGINTVARCHAR(12) 了。
如果省略中间的分号,ipv6 只需要 VARCHAR(32) 就行了。

整型和字符串的转换

  • INET_ATON / INET_NTOA
  • INET6_ATON / INET6_NTOA

#9 MySQL 技巧

2017-04-02

安装之后初始化

忘记密码

改密码

查看版本

  1. mysql 命令行登录的时候显示的欢迎信息
  2. select @@version
  3. select version()
  4. SHOW VARIABLES WHERE variable_name LIKE 'version%';
  5. status

导出数据为 JSON

导出数据为 CSV

#8 MySQL 增加或修改注释

2017-02-19

表注释

SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = '5mzb' AND TABLE_NAME = 'user';
ALTER TABLE `user` COMMENT = '用户';

字段注释

SELECT COLUMN_NAME, COLUMN_COMMENT FROM `information_schema`.`COLUMNS`
WHERE TABLE_SCHEMA = 'sendcloud' AND TABLE_NAME = 'user_info' ORDER BY ORDINAL_POSITION;
ALTER TABLE `user`
CHANGE COLUMN `create_time`
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' AFTER `expire_time`;
ALTER TABLE `user`
MODIFY COLUMN
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' AFTER `expire_time`;

好傻 X 啊,只改个备注,却必须要把字段声明带上,增加出错的可能。

#7 MySQL 索引

2016-02-08
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part:
    col_name [(length)] [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

功能类型

  • 主键索引(PrimaryKey)
  • 唯一索引(UNIQUE)
  • 普通索引(KEY)
  • 全文索引(FULLTEXT)
  • 空间索引(SPATIAL)

算法类型

  • B-Tree
  • Hash
  • R-TREE(空间索引使用)
  • 倒排索引(Inverted Index,全文索引使用)

聚簇索引 Clustered Index

聚簇索引(Clustered Index)是一种特殊的索引类型,它决定了表中数据的物理存储顺序。
在聚簇索引中,数据行按照索引键的顺序存储在磁盘上,因此具有相邻的物理位置,这样可以提高查询效率。
聚簇索引只能有一个,因为它决定了表中数据的物理存储顺序,如果有多个聚簇索引,就会导致数据在磁盘上存储的位置不确定,影响查询效率。

其他索引叫二级索引(Secondary Index),或者辅助索引。

对于 InnoDB,有限使用主键做聚簇索引,其次找一个不含 NULL 值的唯一索引,还没有,就自动生成一个隐式的自增型 ROW_ID 字段(BIGINT UNSIGNED)做聚簇索引 GEN_CLUST_INDEX。

注意:所有索引在提升查询效率的同时,都会影响插入和删除的性能。尤其是聚簇索引需要重新组织数据行的物理存储顺序

#6 MySQL 编码字符集

2015-05-11

各种类型的编码

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | gbk                        |
| character_set_connection | gbk                        |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | gbk                        |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

5.7 及更早版本默认字符集和 Collation 是 latin1 和 latin1_swedish_ci
8.0 之后改成 utf8mb4 和 utf8mb4_0900_ai_ci

上面就有 6 种 全局的编码:

  • client 客户端编码
  • connection 连接编码
  • database 数据库编码,创建表时的默认编码
    load data, 以及创建
  • filesystem 文件系统编码
  • results 结果编码
  • server 服务端编码,创建数据库时的默认编码
  • system 系统编码

还有两种局部的编码:表的编码和列(字段)的编码。
列主要是 char, varchar, text 类型。

字符集

# 都可以 LIKE 搜索
SHOW CHARACTER SET;
SHOW CHARSET;
SHOW CHAR SET;
Charset Description Default collation Maxlen
armscii8 ARMSCII-8 Armenian armscii8_general_ci 1
ascii US ASCII ascii_general_ci 1
big5 Big5 Traditional Chinese big5_chinese_ci 2
binary Binary pseudo charset binary 1
cp1250 Windows Central European cp1250_general_ci 1
cp1251 Windows Cyrillic cp1251_general_ci 1
cp1256 Windows Arabic cp1256_general_ci 1
cp1257 Windows Baltic cp1257_general_ci 1
cp850 DOS West European cp850_general_ci 1
cp852 DOS Central European cp852_general_ci 1
cp866 DOS Russian cp866_general_ci 1
cp932 SJIS for Windows Japanese cp932_japanese_ci 2
dec8 DEC West European dec8_swedish_ci 1
eucjpms UJIS for Windows Japanese eucjpms_japanese_ci 3
euckr EUC-KR Korean euckr_korean_ci 2
gb18030 China National Standard GB18030 gb18030_chinese_ci 4
gb2312 GB2312 Simplified Chinese gb2312_chinese_ci 2
gbk GBK Simplified Chinese gbk_chinese_ci 2
geostd8 GEOSTD8 Georgian geostd8_general_ci 1
greek ISO 8859-7 Greek greek_general_ci 1
hebrew ISO 8859-8 Hebrew hebrew_general_ci 1
hp8 HP West European hp8_english_ci 1
keybcs2 DOS Kamenicky Czech-Slovak keybcs2_general_ci 1
koi8r KOI8-R Relcom Russian koi8r_general_ci 1
koi8u KOI8-U Ukrainian koi8u_general_ci 1
latin1 cp1252 West European latin1_swedish_ci 1
latin2 ISO 8859-2 Central European latin2_general_ci 1
latin5 ISO 8859-9 Turkish latin5_turkish_ci 1
latin7 ISO 8859-13 Baltic latin7_general_ci 1
macce Mac Central European macce_general_ci 1
macroman Mac West European macroman_general_ci 1
sjis Shift-JIS Japanese sjis_japanese_ci 2
swe7 7bit Swedish swe7_swedish_ci 1
tis620 TIS620 Thai tis620_thai_ci 1
ucs2 UCS-2 Unicode ucs2_general_ci 2
ujis EUC-JP Japanese ujis_japanese_ci 3
utf16 UTF-16 Unicode utf16_general_ci 4
utf16le UTF-16LE Unicode utf16le_general_ci 4
utf32 UTF-32 Unicode utf32_general_ci 4
utf8mb3 UTF-8 Unicode utf8mb3_general_ci 3
utf8mb4 UTF-8 Unicode utf8mb4_0900_ai_ci 4
SELECT * FROM information_schema.character_sets;
SELECT * FROM information_schema.character_sets WHERE CHARACTER_SET_NAME LIKE "%utf%";
CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN
utf8mb3 utf8mb3_general_ci UTF-8 Unicode 3
utf16 utf16_general_ci UTF-16 Unicode 4
utf16le utf16le_general_ci UTF-16LE Unicode 4
utf32 utf32_general_ci UTF-32 Unicode 4
utf8mb4 utf8mb4_0900_ai_ci UTF-8 Unicode 4

排序规则

SHOW COLLATION;

这就多了,两百多。

SHOW COLLATION LIKE "%ascii%";
Collation Charset Id Default Compiled Sortlen Pad_attribute
ascii_bin ascii 65 Yes 1 PAD SPACE
ascii_general_ci ascii 11 Yes Yes 1 PAD SPACE
SELECT * FROM information_schema.collations WHERE CHARACTER_SET_NAME = "utf8mb4" AND COLLATION_NAME LIKE "%zh%";
COLLATION_NAME CHARACTER_SET_NAME ID IS_DEFAULT IS_COMPILED SORTLEN PAD_ATTRIBUTE
utf8mb4_zh_0900_as_cs utf8mb4 308 Yes 0 NO PAD

命名规则

字符集名称,语言,通用后缀

  • ai Accent-insensitive 重音不敏感
  • as Accent-sensitive 重音敏感
  • ci Case-insensitive 大小写不敏感
  • cs Case-sensitive 大小写敏感
  • ks Kana-sensitive 假名敏感(日语)
  • bin 二进制

8.0 之后,很多编码中多了 0900 字样,表示 Unicode 9.0 规范。

参考资料与拓展阅读

#4 MySQL set 类型

2014-08-20

顾名思义,就是集合类型。

set('a', 'b', ...)

每个字段可以是指定选项中的若干个(包含 0 个)。

#3 MySQL 时间

2014-03-06

几种形式

  1. 字符串,比如:
  2. varchar(14),存:yyyymmddHHMMSS 格式
  3. varchar(19),存:yyyy-mm-dd HH:MM:SS 格式
  4. varchar(23),存:yyyy-mm-dd HH:MM:SS.fff 格式
  5. varchar(26),存:yyyy-mm-dd HH:MM:SS.ffffff 格式
  6. varchar(24),存:yyyy-mm-ddTHH:MM:SS+0800yyyy-mm-dd HH:MM:SS 0800 格式
  7. 整形数
  8. int 1970 - 2038
    time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime((1 << 31) - 1))
    # 2038-01-19 03:14:07
    
    - int unsigned 1970 - 2106
    time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime((1 << 32) - 1))
    # 2106-02-07 06:28:15
    
    - bigint 可以用到世界毁灭
  9. 注意:也可以任意指定一个时间为起点,比如 2020-01-01 00:00:00
  10. TIMESTAMP 类型
  11. 相当于上面的 int 类型,2038 问题
  12. DATETIME 类型

PS: DATE, TIME, YEAR 三种类型:

  • DATE
  • TIME
  • YEAR

比较

1. 存储效率

2. 时间比较

3. 是否方便索引