数据库

数据库

MySQL如何通过sql查询数据的区间分布情况?

回复

数据库zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 165 次浏览 • 2020-09-07 10:42 • 来自相关话题

MySQL中char、varchar和text三种字段类型的区别和选择

数据库zkbhj 发表了文章 • 0 个评论 • 236 次浏览 • 2020-05-26 10:57 • 来自相关话题

在MySQL中,char、varchar和text类型的字段都可以用来存储字符类型的数据,char、varchar都可以指定最大的字符长度,但text不可以。

它们的存储方式和数据的检索方式也都不一样。

数据的检索效率是:char > varchar > text

具体说明:

char:存储定长数据很方便,CHAR字段上的索引效率级高,必须在括号里定义长度,可以有默认值,比如定义char(10),那么不论你存储的数据是否达到了10个字节,都要占去10个字节的空间(自动用空格填充),且在检索的时候后面的空格会隐藏掉,所以检索出来的数据需要记得用什么trim之类的函数去过滤空格。

varchar:存储变长数据,但存储效率没有CHAR高,必须在括号里定义长度,可以有默认值。保存数据的时候,不进行空格自动填充,而且如果数据存在空格时,当值保存和检索时尾部的空格仍会保留。另外,varchar类型的实际长度是它的值的实际长度+1,这一个字节用于保存实际使用了多大的长度。

text:存储可变长度的非Unicode数据,最大长度为2^31-1个字符。text列不能有默认值,存储或检索过程中,不存在大小写转换,后面如果指定长度,不会报错误,但是这个长度是不起作用的,意思就是你插入数据的时候,超过你指定的长度还是可以正常插入。

关于存储空间:

在使用UTF8字符集的时候,MySQL手册上是这样描述的:

基本拉丁字母、数字和标点符号使用一个字节;
大多数的欧洲和中东手写字母适合两个字节序列:扩展的拉丁字母(包括发音符号、长音符号、重音符号、低音符号和其它音符)、西里尔字母、希腊语、亚美尼亚语、希伯来语、阿拉伯语、叙利亚语和其它语言;
韩语、中文和日本象形文字使用三个字节序列。

结论:

1、经常变化的字段用varchar;

2、知道固定长度的用char;

3、超过255字节的只能用varchar或者text;

4、能用varchar的地方不用text;

5、能够用数字类型的字段尽量选择数字类型而不用字符串类型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了;

6、同一张表出现多个大字段,能合并时尽量合并,不能合并时考虑分表 查看全部
在MySQL中,char、varchar和text类型的字段都可以用来存储字符类型的数据,char、varchar都可以指定最大的字符长度,但text不可以。

它们的存储方式和数据的检索方式也都不一样。

数据的检索效率是:char > varchar > text

具体说明:

char:存储定长数据很方便,CHAR字段上的索引效率级高,必须在括号里定义长度,可以有默认值,比如定义char(10),那么不论你存储的数据是否达到了10个字节,都要占去10个字节的空间(自动用空格填充),且在检索的时候后面的空格会隐藏掉,所以检索出来的数据需要记得用什么trim之类的函数去过滤空格

varchar:存储变长数据,但存储效率没有CHAR高,必须在括号里定义长度,可以有默认值。保存数据的时候,不进行空格自动填充,而且如果数据存在空格时,当值保存和检索时尾部的空格仍会保留。另外,varchar类型的实际长度是它的值的实际长度+1,这一个字节用于保存实际使用了多大的长度。

text:存储可变长度的非Unicode数据,最大长度为2^31-1个字符。text列不能有默认值,存储或检索过程中,不存在大小写转换,后面如果指定长度,不会报错误,但是这个长度是不起作用的,意思就是你插入数据的时候,超过你指定的长度还是可以正常插入。

关于存储空间:

在使用UTF8字符集的时候,MySQL手册上是这样描述的:

基本拉丁字母、数字和标点符号使用一个字节;
大多数的欧洲和中东手写字母适合两个字节序列:扩展的拉丁字母(包括发音符号、长音符号、重音符号、低音符号和其它音符)、西里尔字母、希腊语、亚美尼亚语、希伯来语、阿拉伯语、叙利亚语和其它语言;
韩语、中文和日本象形文字使用三个字节序列。

结论:

1、经常变化的字段用varchar;

2、知道固定长度的用char;

3、超过255字节的只能用varchar或者text;

4、能用varchar的地方不用text;

5、能够用数字类型的字段尽量选择数字类型而不用字符串类型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了;

6、同一张表出现多个大字段,能合并时尽量合并,不能合并时考虑分表

MySQL数据库中指定字段值用特殊字符分割如何处理?

回复

数据库zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 519 次浏览 • 2020-02-05 18:26 • 来自相关话题

MySQL 文本类型,存储大小分别是多少?

回复

数据库zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 789 次浏览 • 2019-11-20 14:48 • 来自相关话题

MySQL中的聚簇索引和非聚簇索引

数据库zkbhj 发表了文章 • 0 个评论 • 396 次浏览 • 2019-10-30 20:12 • 来自相关话题

聚簇索引

数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引(又叫二级索引)两种。Innodb的聚簇索引在同一个B-Tree中保存了索引列和具体的数据,在聚簇索引中,实际的数据保存在叶子页中,中间的节点页保存指向下一层页面的指针。“聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。一个表只能有一个聚簇索引,因为在一个表中数据的存放方式只有一种。

一般来说,将通过主键作为聚簇索引的索引列,也就是通过主键聚集数据。下图展示了Innodb中聚簇索引的结构(图片来自《高性能MySQL(第三版)》):





 
这里要特别注意页的概念,一个页可以理解为一块具有一定大小的连续的存储区域。相同页内的数据行在物理上是相邻的,因此逻辑上键值相邻的页在物理上可能相隔很远。

在中间的某个节点页中,主键<11的叶子页和11<主键<21的叶子页分别被两个指针所指向,且主键<11的叶子页也有一个指针指向了11<主键<21的叶子页,其余页之间的关系也是一样。

聚簇索引的优点

聚簇索引将索引和数据行保存在同一个B-Tree中,查询通过聚簇索引可以直接获取数据,相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率要高。
聚簇索引对于范围查询的效率很高,因为其数据是按照大小排列的,


聚簇索引的缺点

聚簇索引的更新代价比较高,如果更新了行的聚簇索引列,就需要将数据移动到相应的位置。这可能因为要插入的页已满而导致“页分裂”。
插入速度严重依赖于插入顺序,按照主键进行插入的速度是加载数据到Innodb中的最快方式。如果不是按照主键插入,最好在加载完成后使用OPTIMIZE TABLE命令重新组织一下表。
聚簇索引在插入新行和更新主键时,可能导致“页分裂”问题。
聚簇索引可能导致全表扫描速度变慢,因为可能需要加载物理上相隔较远的页到内存中(需要耗时的磁盘寻道操作)。


非聚簇索引

非聚簇索引,又叫二级索引。二级索引的叶子节点中保存的不是指向行的物理指针,而是行的主键值。当通过二级索引查找行,存储引擎需要在二级索引中找到相应的叶子节点,获得行的主键值,然后使用主键去聚簇索引中查找数据行,这需要两次B-Tree查找。

总结

下面是Innodb聚簇索引和非聚簇索引的示意图(图片来自《高性能MySQL(第三版)》:




  查看全部
聚簇索引

数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引(又叫二级索引)两种。Innodb的聚簇索引在同一个B-Tree中保存了索引列和具体的数据,在聚簇索引中,实际的数据保存在叶子页中,中间的节点页保存指向下一层页面的指针。“聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。一个表只能有一个聚簇索引,因为在一个表中数据的存放方式只有一种。

一般来说,将通过主键作为聚簇索引的索引列,也就是通过主键聚集数据。下图展示了Innodb中聚簇索引的结构(图片来自《高性能MySQL(第三版)》):

mysql_index_2.png

 
这里要特别注意页的概念,一个页可以理解为一块具有一定大小的连续的存储区域。相同页内的数据行在物理上是相邻的,因此逻辑上键值相邻的页在物理上可能相隔很远。

在中间的某个节点页中,主键<11的叶子页和11<主键<21的叶子页分别被两个指针所指向,且主键<11的叶子页也有一个指针指向了11<主键<21的叶子页,其余页之间的关系也是一样。

聚簇索引的优点


聚簇索引将索引和数据行保存在同一个B-Tree中,查询通过聚簇索引可以直接获取数据,相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率要高。
聚簇索引对于范围查询的效率很高,因为其数据是按照大小排列的,



聚簇索引的缺点


聚簇索引的更新代价比较高,如果更新了行的聚簇索引列,就需要将数据移动到相应的位置。这可能因为要插入的页已满而导致“页分裂”。
插入速度严重依赖于插入顺序,按照主键进行插入的速度是加载数据到Innodb中的最快方式。如果不是按照主键插入,最好在加载完成后使用OPTIMIZE TABLE命令重新组织一下表。
聚簇索引在插入新行和更新主键时,可能导致“页分裂”问题。
聚簇索引可能导致全表扫描速度变慢,因为可能需要加载物理上相隔较远的页到内存中(需要耗时的磁盘寻道操作)。



非聚簇索引

非聚簇索引,又叫二级索引。二级索引的叶子节点中保存的不是指向行的物理指针,而是行的主键值。当通过二级索引查找行,存储引擎需要在二级索引中找到相应的叶子节点,获得行的主键值,然后使用主键去聚簇索引中查找数据行,这需要两次B-Tree查找。

总结

下面是Innodb聚簇索引和非聚簇索引的示意图(图片来自《高性能MySQL(第三版)》:
mysql_index_3.png

 

MySQL中如何获取13位的时间戳?

回复

数据库zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 822 次浏览 • 2019-09-26 09:59 • 来自相关话题

如何在MySQL中快速筛除前缀相同的表?

回复

数据库zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 787 次浏览 • 2019-09-25 11:35 • 来自相关话题

如何查看MySQL的版本?

回复

数据库zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 899 次浏览 • 2019-09-11 14:34 • 来自相关话题

MySQL数据类型详解之JSON

数据库zkbhj 发表了文章 • 0 个评论 • 523 次浏览 • 2019-09-11 13:57 • 来自相关话题

1、引入版本:5.7.8。

As of MySQL 5.7.8, MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column:

2、关于MySQL的JSON类型

JSON估计大家伙都熟悉了,我就不再介绍这方面内容。在5.7这个版本之前如果想在表中保存JSON格式类型的数据,则需要依靠varchar或者text之类的数据类型,如果在低于5.7版本的数据库中使用了JSON类型来建表,显然是不会成功的。
 3、JSON数据类型意义

其实,没有JSON数据类型的支持,我们一样可以通过varchar类型或者text等类型来保存这一格式的数据,但是,为什么还要专门增加这一数据格式的支持呢?其中肯定有较varchar或者text来存储此类型更优越的地方。
保证了JSON数据类型的强校验,JSON数据列会自动校验存入此列的内容是否符合JSON格式,非正常格式则报错,而varchar类型和text等类型本身是不存在这种机制的。MySQL同时提供了一组操作JSON类型数据的内置函数。更优化的存储格式,存储在JSON列中的JSON数据会被转成内部特定的存储格式,允许快速读取。可以基于JSON格式的特征支持修改特定的键值。(即不需要把整条内容拿出来放到程序中遍历然后寻找替换再塞回去,MySQL内置的函数允许你通过一条SQL语句就能搞定)
3、如何使用JSON类型

建表

在MySQL中创建具有JSON数据列的表,其实和数据类型没有太大区别,具体举例如下CREATE TABLE tab_base_info (
id BIGINT NOT NULL PRIMARY KEY auto_increment,
content json
);新增数据

插入一条语句,注意看JSON数据列的内容:
INSERT INTO tab_base_info (content)
VALUES
(
'{"author": "zkbhj", "blog": "https://www.zkbhj.com/"}'
);这里需要提醒的是:
JSON列存储的数据要么是NULL,要么必须是JSON格式数据,否则会报错。JSON数据类型是没有默认值的(声明时"DEFAULT NULL")。
 
5、MySQL关于JSON的内置函数

MySQL关于JSON数据格式的操作提供了很多高效率的内置函数,我们可以从MySQL官网上找到很详细的介绍和使用说明。 
从JSON功能介绍的主页也可以看到,这些内置函数支持我们创建、查找、替换和返回值等相关的操作,像我们替换指定内容的操作就可以使用JSON_REPLACE()这个函数,不过最后实现通过纯SQL语句执行最终的内容替换,你还需要通过执行UPDATE语句,比如:
UPDATE tab_base_info
SET content = json_replace(content, '$.author', "tom")
WHERE id = 1;
 其中“$.***”表示找到JSON内容中匹配的修改字段。

更多关于这些内置函数的用法,大家都可以到官网(链接请查看本文参考资料)的文档上去查阅,写的十分详细而且还有举例。 
官方文档:https://dev.mysql.com/doc/refman/5.7/en/json.html
  查看全部
1、引入版本:5.7.8。


As of MySQL 5.7.8, MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column:


2、关于MySQL的JSON类型

JSON估计大家伙都熟悉了,我就不再介绍这方面内容。在5.7这个版本之前如果想在表中保存JSON格式类型的数据,则需要依靠varchar或者text之类的数据类型,如果在低于5.7版本的数据库中使用了JSON类型来建表,显然是不会成功的。
 3、JSON数据类型意义

其实,没有JSON数据类型的支持,我们一样可以通过varchar类型或者text等类型来保存这一格式的数据,但是,为什么还要专门增加这一数据格式的支持呢?其中肯定有较varchar或者text来存储此类型更优越的地方。
  • 保证了JSON数据类型的强校验,JSON数据列会自动校验存入此列的内容是否符合JSON格式,非正常格式则报错,而varchar类型和text等类型本身是不存在这种机制的。
  • MySQL同时提供了一组操作JSON类型数据的内置函数。
  • 更优化的存储格式,存储在JSON列中的JSON数据会被转成内部特定的存储格式,允许快速读取。
  • 可以基于JSON格式的特征支持修改特定的键值。(即不需要把整条内容拿出来放到程序中遍历然后寻找替换再塞回去,MySQL内置的函数允许你通过一条SQL语句就能搞定)

3、如何使用JSON类型

建表

在MySQL中创建具有JSON数据列的表,其实和数据类型没有太大区别,具体举例如下
CREATE TABLE tab_base_info (
id BIGINT NOT NULL PRIMARY KEY auto_increment,
content json
);
新增数据

插入一条语句,注意看JSON数据列的内容:
INSERT INTO tab_base_info (content)
VALUES
(
'{"author": "zkbhj", "blog": "https://www.zkbhj.com/"}'
);
这里需要提醒的是:
  • JSON列存储的数据要么是NULL,要么必须是JSON格式数据,否则会报错。
  • JSON数据类型是没有默认值的(声明时"DEFAULT NULL")。

 
5、MySQL关于JSON的内置函数

MySQL关于JSON数据格式的操作提供了很多高效率的内置函数,我们可以从MySQL官网上找到很详细的介绍和使用说明。 
从JSON功能介绍的主页也可以看到,这些内置函数支持我们创建、查找、替换和返回值等相关的操作,像我们替换指定内容的操作就可以使用JSON_REPLACE()这个函数,不过最后实现通过纯SQL语句执行最终的内容替换,你还需要通过执行UPDATE语句,比如:
UPDATE tab_base_info 
SET content = json_replace(content, '$.author', "tom")
WHERE id = 1;

 其中“$.***”表示找到JSON内容中匹配的修改字段。

更多关于这些内置函数的用法,大家都可以到官网(链接请查看本文参考资料)的文档上去查阅,写的十分详细而且还有举例。 
官方文档:https://dev.mysql.com/doc/refman/5.7/en/json.html
 

MySQL如何判断字符串或时间戳最后两个字符?

回复

数据库zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 904 次浏览 • 2019-08-21 10:46 • 来自相关话题

58到家数据库30条军规解读

数据库zkbhj 发表了文章 • 0 个评论 • 890 次浏览 • 2017-02-16 10:29 • 来自相关话题

一、基础规范

(1)必须使用InnoDB存储引擎

解读:支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高

 

(2)必须使用UTF8字符集

解读:万国码,无需转码,无乱码风险,节省空间

 

(3)数据表、数据字段必须加入中文注释

解读:N年后谁tm知道这个r1,r2,r3字段是干嘛的

 

(4)禁止使用存储过程、视图、触发器、Event

解读:高并发大数据的互联网业务,架构设计思路是“解放数据库CPU,将计算转移到服务层”,并发量大的情况下,这些功能很可能将数据库拖死,业务逻辑放到服务层具备更好的扩展性,能够轻易实现“增机器就加性能”。数据库擅长存储与索引,CPU计算还是上移吧

 

(5)禁止存储大文件或者大照片

解读:为何要让数据库做它不擅长的事情?大文件和照片存储在文件系统,数据库里存URI多好

 

二、命名规范

(6)只允许使用内网域名,而不是ip连接数据库

 

(7)线上环境、开发环境、测试环境数据库内网域名遵循命名规范

业务名称:xxx

线上环境:dj.xxx.db

开发环境:dj.xxx.rdb

测试环境:dj.xxx.tdb

从库在名称后加-s标识,备库在名称后加-ss标识

线上从库:dj.xxx-s.db

线上备库:dj.xxx-sss.db

 

(8)库名、表名、字段名:小写,下划线风格,不超过32个字符,必须见名知意,禁止拼音英文混用

 

(9)表名t_xxx,非唯一索引名idx_xxx,唯一索引名uniq_xxx

 

三、表设计规范

(10)单实例表数目必须小于500

 

(11)单表列数目必须小于30

 

(12)表必须有主键,例如自增主键

解读:

a)主键递增,数据行写入可以提高插入性能,可以避免page分裂,减少表碎片提升空间和内存的使用

b)主键要选择较短的数据类型, Innodb引擎普通索引都会保存主键的值,较短的数据类型可以有效的减少索引的磁盘空间,提高索引的缓存效率

c) 无主键的表删除,在row模式的主从架构,会导致备库夯住

 

(13)禁止使用外键,如果有外键完整性约束,需要应用程序控制

解读:外键会导致表与表之间耦合,update与delete操作都会涉及相关联的表,十分影响sql 的性能,甚至会造成死锁。高并发情况下容易造成数据库性能,大数据高并发业务场景数据库使用以性能优先

 

四、字段设计规范

(14)必须把字段定义为NOT NULL并且提供默认值

解读:

a)null的列使索引/索引统计/值比较都更加复杂,对MySQL来说更难优化

b)null 这种类型MySQL内部需要进行特殊处理,增加数据库处理记录的复杂性;同等条件下,表中有较多空字段的时候,数据库的处理性能会降低很多

c)null值需要更多的存储空,无论是表还是索引中每行中的null的列都需要额外的空间来标识

d)对null 的处理时候,只能采用is null或is not null,而不能采用=、in、<、<>、!=、not in这些操作符号。如:where name!=’shenjian’,如果存在name为null值的记录,查询结果就不会包含name为null值的记录

 

(15)禁止使用TEXT、BLOB类型

解读:会浪费更多的磁盘和内存空间,非必要的大量的大字段查询会淘汰掉热数据,导致内存命中率急剧降低,影响数据库性能

 

(16)禁止使用小数存储货币

解读:使用整数吧,小数容易导致钱对不上

 

(17)必须使用varchar(20)存储手机号

解读:

a)涉及到区号或者国家代号,可能出现+-()

b)手机号会去做数学运算么?

c)varchar可以支持模糊查询,例如:like“138%”

 

(18)禁止使用ENUM,可使用TINYINT代替

解读:

a)增加新的ENUM值要做DDL操作

b)ENUM的内部实际存储就是整数,你以为自己定义的是字符串?

 

五、索引设计规范

(19)单表索引建议控制在5个以内

 

(20)单索引字段数不允许超过5个

解读:字段超过5个时,实际已经起不到有效过滤数据的作用了

 

(21)禁止在更新十分频繁、区分度不高的属性上建立索引

解读:

a)更新会变更B+树,更新频繁的字段建立索引会大大降低数据库性能

b)“性别”这种区分度不大的属性,建立索引是没有什么意义的,不能有效过滤数据,性能与全表扫描类似

 

(22)建立组合索引,必须把区分度高的字段放在前面

解读:能够更加有效的过滤数据

 

六、SQL使用规范

(23)禁止使用SELECT *,只获取必要的字段,需要显示说明列属性

解读:

a)读取不需要的列会增加CPU、IO、NET消耗

b)不能有效的利用覆盖索引

c)使用SELECT *容易在增加或者删除字段后出现程序BUG

 

(24)禁止使用INSERT INTO t_xxx VALUES(xxx),必须显示指定插入的列属性

解读:容易在增加或者删除字段后出现程序BUG

 

(25)禁止使用属性隐式转换

解读:SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描,而不能命中phone索引,猜猜为什么?(这个线上问题不止出现过一次)

 

(26)禁止在WHERE条件的属性上使用函数或者表达式

解读:SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描

正确的写法是:SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')

 

(27)禁止负向查询,以及%开头的模糊查询

解读:

a)负向查询条件:NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等,会导致全表扫描

b)%开头的模糊查询,会导致全表扫描

 

(28)禁止大表使用JOIN查询,禁止大表使用子查询

解读:会产生临时表,消耗较多内存与CPU,极大影响数据库性能

 

(29)禁止使用OR条件,必须改为IN查询

解读:旧版本Mysql的OR查询是不能命中索引的,即使能命中索引,为何要让数据库耗费更多的CPU帮助实施查询优化呢?

 

(30)应用程序必须捕获SQL异常,并有相应处理


总结:大数据量高并发的互联网业务,极大影响数据库性能的都不让用,不让用哟。 查看全部
一、基础规范

(1)必须使用InnoDB存储引擎

解读:支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高

 

(2)必须使用UTF8字符集

解读:万国码,无需转码,无乱码风险,节省空间

 

(3)数据表、数据字段必须加入中文注释

解读:N年后谁tm知道这个r1,r2,r3字段是干嘛的

 

(4)禁止使用存储过程、视图、触发器、Event

解读:高并发大数据的互联网业务,架构设计思路是“解放数据库CPU,将计算转移到服务层”,并发量大的情况下,这些功能很可能将数据库拖死,业务逻辑放到服务层具备更好的扩展性,能够轻易实现“增机器就加性能”。数据库擅长存储与索引,CPU计算还是上移吧

 

(5)禁止存储大文件或者大照片

解读:为何要让数据库做它不擅长的事情?大文件和照片存储在文件系统,数据库里存URI多好

 

二、命名规范

(6)只允许使用内网域名,而不是ip连接数据库

 

(7)线上环境、开发环境、测试环境数据库内网域名遵循命名规范

业务名称:xxx

线上环境:dj.xxx.db

开发环境:dj.xxx.rdb

测试环境:dj.xxx.tdb

从库在名称后加-s标识,备库在名称后加-ss标识

线上从库:dj.xxx-s.db

线上备库:dj.xxx-sss.db

 

(8)库名、表名、字段名:小写,下划线风格,不超过32个字符,必须见名知意,禁止拼音英文混用

 

(9)表名t_xxx,非唯一索引名idx_xxx,唯一索引名uniq_xxx

 

三、表设计规范

(10)单实例表数目必须小于500

 

(11)单表列数目必须小于30

 

(12)表必须有主键,例如自增主键

解读:

a)主键递增,数据行写入可以提高插入性能,可以避免page分裂,减少表碎片提升空间和内存的使用

b)主键要选择较短的数据类型, Innodb引擎普通索引都会保存主键的值,较短的数据类型可以有效的减少索引的磁盘空间,提高索引的缓存效率

c) 无主键的表删除,在row模式的主从架构,会导致备库夯住

 

(13)禁止使用外键,如果有外键完整性约束,需要应用程序控制

解读:外键会导致表与表之间耦合,update与delete操作都会涉及相关联的表,十分影响sql 的性能,甚至会造成死锁。高并发情况下容易造成数据库性能,大数据高并发业务场景数据库使用以性能优先

 

四、字段设计规范

(14)必须把字段定义为NOT NULL并且提供默认值

解读:

a)null的列使索引/索引统计/值比较都更加复杂,对MySQL来说更难优化

b)null 这种类型MySQL内部需要进行特殊处理,增加数据库处理记录的复杂性;同等条件下,表中有较多空字段的时候,数据库的处理性能会降低很多

c)null值需要更多的存储空,无论是表还是索引中每行中的null的列都需要额外的空间来标识

d)对null 的处理时候,只能采用is null或is not null,而不能采用=、in、<、<>、!=、not in这些操作符号。如:where name!=’shenjian’,如果存在name为null值的记录,查询结果就不会包含name为null值的记录

 

(15)禁止使用TEXT、BLOB类型

解读:会浪费更多的磁盘和内存空间,非必要的大量的大字段查询会淘汰掉热数据,导致内存命中率急剧降低,影响数据库性能

 

(16)禁止使用小数存储货币

解读:使用整数吧,小数容易导致钱对不上

 

(17)必须使用varchar(20)存储手机号

解读:

a)涉及到区号或者国家代号,可能出现+-()

b)手机号会去做数学运算么?

c)varchar可以支持模糊查询,例如:like“138%”

 

(18)禁止使用ENUM,可使用TINYINT代替

解读:

a)增加新的ENUM值要做DDL操作

b)ENUM的内部实际存储就是整数,你以为自己定义的是字符串?

 

五、索引设计规范

(19)单表索引建议控制在5个以内

 

(20)单索引字段数不允许超过5个

解读:字段超过5个时,实际已经起不到有效过滤数据的作用了

 

(21)禁止在更新十分频繁、区分度不高的属性上建立索引

解读:

a)更新会变更B+树,更新频繁的字段建立索引会大大降低数据库性能

b)“性别”这种区分度不大的属性,建立索引是没有什么意义的,不能有效过滤数据,性能与全表扫描类似

 

(22)建立组合索引,必须把区分度高的字段放在前面

解读:能够更加有效的过滤数据

 

六、SQL使用规范

(23)禁止使用SELECT *,只获取必要的字段,需要显示说明列属性

解读:

a)读取不需要的列会增加CPU、IO、NET消耗

b)不能有效的利用覆盖索引

c)使用SELECT *容易在增加或者删除字段后出现程序BUG

 

(24)禁止使用INSERT INTO t_xxx VALUES(xxx),必须显示指定插入的列属性

解读:容易在增加或者删除字段后出现程序BUG

 

(25)禁止使用属性隐式转换

解读:SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描,而不能命中phone索引,猜猜为什么?(这个线上问题不止出现过一次)

 

(26)禁止在WHERE条件的属性上使用函数或者表达式

解读:SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描

正确的写法是:SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')

 

(27)禁止负向查询,以及%开头的模糊查询

解读:

a)负向查询条件:NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等,会导致全表扫描

b)%开头的模糊查询,会导致全表扫描

 

(28)禁止大表使用JOIN查询,禁止大表使用子查询

解读:会产生临时表,消耗较多内存与CPU,极大影响数据库性能

 

(29)禁止使用OR条件,必须改为IN查询

解读:旧版本Mysql的OR查询是不能命中索引的,即使能命中索引,为何要让数据库耗费更多的CPU帮助实施查询优化呢?

 

(30)应用程序必须捕获SQL异常,并有相应处理


总结:大数据量高并发的互联网业务,极大影响数据库性能的都不让用,不让用哟。

MySQL如何通过sql查询数据的区间分布情况?

回复

数据库zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 165 次浏览 • 2020-09-07 10:42 • 来自相关话题

MySQL数据库中指定字段值用特殊字符分割如何处理?

回复

数据库zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 519 次浏览 • 2020-02-05 18:26 • 来自相关话题

MySQL 文本类型,存储大小分别是多少?

回复

数据库zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 789 次浏览 • 2019-11-20 14:48 • 来自相关话题

MySQL中如何获取13位的时间戳?

回复

数据库zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 822 次浏览 • 2019-09-26 09:59 • 来自相关话题

如何在MySQL中快速筛除前缀相同的表?

回复

数据库zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 787 次浏览 • 2019-09-25 11:35 • 来自相关话题

如何查看MySQL的版本?

回复

数据库zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 899 次浏览 • 2019-09-11 14:34 • 来自相关话题

MySQL如何判断字符串或时间戳最后两个字符?

回复

数据库zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 904 次浏览 • 2019-08-21 10:46 • 来自相关话题

MySql如何将13位的时间戳展示成易读格式?

回复

数据库zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 1182 次浏览 • 2019-01-10 19:20 • 来自相关话题

CSV文件数据导入MySQL数据库时中文乱码如何解决?

回复

数据库zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 1212 次浏览 • 2018-12-04 10:13 • 来自相关话题

MySQL如何清空数据表并且自增id从0开始?

回复

数据库zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 1041 次浏览 • 2018-11-13 21:06 • 来自相关话题

MySQL中char、varchar和text三种字段类型的区别和选择

数据库zkbhj 发表了文章 • 0 个评论 • 236 次浏览 • 2020-05-26 10:57 • 来自相关话题

在MySQL中,char、varchar和text类型的字段都可以用来存储字符类型的数据,char、varchar都可以指定最大的字符长度,但text不可以。

它们的存储方式和数据的检索方式也都不一样。

数据的检索效率是:char > varchar > text

具体说明:

char:存储定长数据很方便,CHAR字段上的索引效率级高,必须在括号里定义长度,可以有默认值,比如定义char(10),那么不论你存储的数据是否达到了10个字节,都要占去10个字节的空间(自动用空格填充),且在检索的时候后面的空格会隐藏掉,所以检索出来的数据需要记得用什么trim之类的函数去过滤空格。

varchar:存储变长数据,但存储效率没有CHAR高,必须在括号里定义长度,可以有默认值。保存数据的时候,不进行空格自动填充,而且如果数据存在空格时,当值保存和检索时尾部的空格仍会保留。另外,varchar类型的实际长度是它的值的实际长度+1,这一个字节用于保存实际使用了多大的长度。

text:存储可变长度的非Unicode数据,最大长度为2^31-1个字符。text列不能有默认值,存储或检索过程中,不存在大小写转换,后面如果指定长度,不会报错误,但是这个长度是不起作用的,意思就是你插入数据的时候,超过你指定的长度还是可以正常插入。

关于存储空间:

在使用UTF8字符集的时候,MySQL手册上是这样描述的:

基本拉丁字母、数字和标点符号使用一个字节;
大多数的欧洲和中东手写字母适合两个字节序列:扩展的拉丁字母(包括发音符号、长音符号、重音符号、低音符号和其它音符)、西里尔字母、希腊语、亚美尼亚语、希伯来语、阿拉伯语、叙利亚语和其它语言;
韩语、中文和日本象形文字使用三个字节序列。

结论:

1、经常变化的字段用varchar;

2、知道固定长度的用char;

3、超过255字节的只能用varchar或者text;

4、能用varchar的地方不用text;

5、能够用数字类型的字段尽量选择数字类型而不用字符串类型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了;

6、同一张表出现多个大字段,能合并时尽量合并,不能合并时考虑分表 查看全部
在MySQL中,char、varchar和text类型的字段都可以用来存储字符类型的数据,char、varchar都可以指定最大的字符长度,但text不可以。

它们的存储方式和数据的检索方式也都不一样。

数据的检索效率是:char > varchar > text

具体说明:

char:存储定长数据很方便,CHAR字段上的索引效率级高,必须在括号里定义长度,可以有默认值,比如定义char(10),那么不论你存储的数据是否达到了10个字节,都要占去10个字节的空间(自动用空格填充),且在检索的时候后面的空格会隐藏掉,所以检索出来的数据需要记得用什么trim之类的函数去过滤空格

varchar:存储变长数据,但存储效率没有CHAR高,必须在括号里定义长度,可以有默认值。保存数据的时候,不进行空格自动填充,而且如果数据存在空格时,当值保存和检索时尾部的空格仍会保留。另外,varchar类型的实际长度是它的值的实际长度+1,这一个字节用于保存实际使用了多大的长度。

text:存储可变长度的非Unicode数据,最大长度为2^31-1个字符。text列不能有默认值,存储或检索过程中,不存在大小写转换,后面如果指定长度,不会报错误,但是这个长度是不起作用的,意思就是你插入数据的时候,超过你指定的长度还是可以正常插入。

关于存储空间:

在使用UTF8字符集的时候,MySQL手册上是这样描述的:

基本拉丁字母、数字和标点符号使用一个字节;
大多数的欧洲和中东手写字母适合两个字节序列:扩展的拉丁字母(包括发音符号、长音符号、重音符号、低音符号和其它音符)、西里尔字母、希腊语、亚美尼亚语、希伯来语、阿拉伯语、叙利亚语和其它语言;
韩语、中文和日本象形文字使用三个字节序列。

结论:

1、经常变化的字段用varchar;

2、知道固定长度的用char;

3、超过255字节的只能用varchar或者text;

4、能用varchar的地方不用text;

5、能够用数字类型的字段尽量选择数字类型而不用字符串类型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了;

6、同一张表出现多个大字段,能合并时尽量合并,不能合并时考虑分表

MySQL中的聚簇索引和非聚簇索引

数据库zkbhj 发表了文章 • 0 个评论 • 396 次浏览 • 2019-10-30 20:12 • 来自相关话题

聚簇索引

数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引(又叫二级索引)两种。Innodb的聚簇索引在同一个B-Tree中保存了索引列和具体的数据,在聚簇索引中,实际的数据保存在叶子页中,中间的节点页保存指向下一层页面的指针。“聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。一个表只能有一个聚簇索引,因为在一个表中数据的存放方式只有一种。

一般来说,将通过主键作为聚簇索引的索引列,也就是通过主键聚集数据。下图展示了Innodb中聚簇索引的结构(图片来自《高性能MySQL(第三版)》):





 
这里要特别注意页的概念,一个页可以理解为一块具有一定大小的连续的存储区域。相同页内的数据行在物理上是相邻的,因此逻辑上键值相邻的页在物理上可能相隔很远。

在中间的某个节点页中,主键<11的叶子页和11<主键<21的叶子页分别被两个指针所指向,且主键<11的叶子页也有一个指针指向了11<主键<21的叶子页,其余页之间的关系也是一样。

聚簇索引的优点

聚簇索引将索引和数据行保存在同一个B-Tree中,查询通过聚簇索引可以直接获取数据,相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率要高。
聚簇索引对于范围查询的效率很高,因为其数据是按照大小排列的,


聚簇索引的缺点

聚簇索引的更新代价比较高,如果更新了行的聚簇索引列,就需要将数据移动到相应的位置。这可能因为要插入的页已满而导致“页分裂”。
插入速度严重依赖于插入顺序,按照主键进行插入的速度是加载数据到Innodb中的最快方式。如果不是按照主键插入,最好在加载完成后使用OPTIMIZE TABLE命令重新组织一下表。
聚簇索引在插入新行和更新主键时,可能导致“页分裂”问题。
聚簇索引可能导致全表扫描速度变慢,因为可能需要加载物理上相隔较远的页到内存中(需要耗时的磁盘寻道操作)。


非聚簇索引

非聚簇索引,又叫二级索引。二级索引的叶子节点中保存的不是指向行的物理指针,而是行的主键值。当通过二级索引查找行,存储引擎需要在二级索引中找到相应的叶子节点,获得行的主键值,然后使用主键去聚簇索引中查找数据行,这需要两次B-Tree查找。

总结

下面是Innodb聚簇索引和非聚簇索引的示意图(图片来自《高性能MySQL(第三版)》:




  查看全部
聚簇索引

数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引(又叫二级索引)两种。Innodb的聚簇索引在同一个B-Tree中保存了索引列和具体的数据,在聚簇索引中,实际的数据保存在叶子页中,中间的节点页保存指向下一层页面的指针。“聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。一个表只能有一个聚簇索引,因为在一个表中数据的存放方式只有一种。

一般来说,将通过主键作为聚簇索引的索引列,也就是通过主键聚集数据。下图展示了Innodb中聚簇索引的结构(图片来自《高性能MySQL(第三版)》):

mysql_index_2.png

 
这里要特别注意页的概念,一个页可以理解为一块具有一定大小的连续的存储区域。相同页内的数据行在物理上是相邻的,因此逻辑上键值相邻的页在物理上可能相隔很远。

在中间的某个节点页中,主键<11的叶子页和11<主键<21的叶子页分别被两个指针所指向,且主键<11的叶子页也有一个指针指向了11<主键<21的叶子页,其余页之间的关系也是一样。

聚簇索引的优点


聚簇索引将索引和数据行保存在同一个B-Tree中,查询通过聚簇索引可以直接获取数据,相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率要高。
聚簇索引对于范围查询的效率很高,因为其数据是按照大小排列的,



聚簇索引的缺点


聚簇索引的更新代价比较高,如果更新了行的聚簇索引列,就需要将数据移动到相应的位置。这可能因为要插入的页已满而导致“页分裂”。
插入速度严重依赖于插入顺序,按照主键进行插入的速度是加载数据到Innodb中的最快方式。如果不是按照主键插入,最好在加载完成后使用OPTIMIZE TABLE命令重新组织一下表。
聚簇索引在插入新行和更新主键时,可能导致“页分裂”问题。
聚簇索引可能导致全表扫描速度变慢,因为可能需要加载物理上相隔较远的页到内存中(需要耗时的磁盘寻道操作)。



非聚簇索引

非聚簇索引,又叫二级索引。二级索引的叶子节点中保存的不是指向行的物理指针,而是行的主键值。当通过二级索引查找行,存储引擎需要在二级索引中找到相应的叶子节点,获得行的主键值,然后使用主键去聚簇索引中查找数据行,这需要两次B-Tree查找。

总结

下面是Innodb聚簇索引和非聚簇索引的示意图(图片来自《高性能MySQL(第三版)》:
mysql_index_3.png

 

MySQL数据类型详解之JSON

数据库zkbhj 发表了文章 • 0 个评论 • 523 次浏览 • 2019-09-11 13:57 • 来自相关话题

1、引入版本:5.7.8。

As of MySQL 5.7.8, MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column:

2、关于MySQL的JSON类型

JSON估计大家伙都熟悉了,我就不再介绍这方面内容。在5.7这个版本之前如果想在表中保存JSON格式类型的数据,则需要依靠varchar或者text之类的数据类型,如果在低于5.7版本的数据库中使用了JSON类型来建表,显然是不会成功的。
 3、JSON数据类型意义

其实,没有JSON数据类型的支持,我们一样可以通过varchar类型或者text等类型来保存这一格式的数据,但是,为什么还要专门增加这一数据格式的支持呢?其中肯定有较varchar或者text来存储此类型更优越的地方。
保证了JSON数据类型的强校验,JSON数据列会自动校验存入此列的内容是否符合JSON格式,非正常格式则报错,而varchar类型和text等类型本身是不存在这种机制的。MySQL同时提供了一组操作JSON类型数据的内置函数。更优化的存储格式,存储在JSON列中的JSON数据会被转成内部特定的存储格式,允许快速读取。可以基于JSON格式的特征支持修改特定的键值。(即不需要把整条内容拿出来放到程序中遍历然后寻找替换再塞回去,MySQL内置的函数允许你通过一条SQL语句就能搞定)
3、如何使用JSON类型

建表

在MySQL中创建具有JSON数据列的表,其实和数据类型没有太大区别,具体举例如下CREATE TABLE tab_base_info (
id BIGINT NOT NULL PRIMARY KEY auto_increment,
content json
);新增数据

插入一条语句,注意看JSON数据列的内容:
INSERT INTO tab_base_info (content)
VALUES
(
'{"author": "zkbhj", "blog": "https://www.zkbhj.com/"}'
);这里需要提醒的是:
JSON列存储的数据要么是NULL,要么必须是JSON格式数据,否则会报错。JSON数据类型是没有默认值的(声明时"DEFAULT NULL")。
 
5、MySQL关于JSON的内置函数

MySQL关于JSON数据格式的操作提供了很多高效率的内置函数,我们可以从MySQL官网上找到很详细的介绍和使用说明。 
从JSON功能介绍的主页也可以看到,这些内置函数支持我们创建、查找、替换和返回值等相关的操作,像我们替换指定内容的操作就可以使用JSON_REPLACE()这个函数,不过最后实现通过纯SQL语句执行最终的内容替换,你还需要通过执行UPDATE语句,比如:
UPDATE tab_base_info
SET content = json_replace(content, '$.author', "tom")
WHERE id = 1;
 其中“$.***”表示找到JSON内容中匹配的修改字段。

更多关于这些内置函数的用法,大家都可以到官网(链接请查看本文参考资料)的文档上去查阅,写的十分详细而且还有举例。 
官方文档:https://dev.mysql.com/doc/refman/5.7/en/json.html
  查看全部
1、引入版本:5.7.8。


As of MySQL 5.7.8, MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column:


2、关于MySQL的JSON类型

JSON估计大家伙都熟悉了,我就不再介绍这方面内容。在5.7这个版本之前如果想在表中保存JSON格式类型的数据,则需要依靠varchar或者text之类的数据类型,如果在低于5.7版本的数据库中使用了JSON类型来建表,显然是不会成功的。
 3、JSON数据类型意义

其实,没有JSON数据类型的支持,我们一样可以通过varchar类型或者text等类型来保存这一格式的数据,但是,为什么还要专门增加这一数据格式的支持呢?其中肯定有较varchar或者text来存储此类型更优越的地方。
  • 保证了JSON数据类型的强校验,JSON数据列会自动校验存入此列的内容是否符合JSON格式,非正常格式则报错,而varchar类型和text等类型本身是不存在这种机制的。
  • MySQL同时提供了一组操作JSON类型数据的内置函数。
  • 更优化的存储格式,存储在JSON列中的JSON数据会被转成内部特定的存储格式,允许快速读取。
  • 可以基于JSON格式的特征支持修改特定的键值。(即不需要把整条内容拿出来放到程序中遍历然后寻找替换再塞回去,MySQL内置的函数允许你通过一条SQL语句就能搞定)

3、如何使用JSON类型

建表

在MySQL中创建具有JSON数据列的表,其实和数据类型没有太大区别,具体举例如下
CREATE TABLE tab_base_info (
id BIGINT NOT NULL PRIMARY KEY auto_increment,
content json
);
新增数据

插入一条语句,注意看JSON数据列的内容:
INSERT INTO tab_base_info (content)
VALUES
(
'{"author": "zkbhj", "blog": "https://www.zkbhj.com/"}'
);
这里需要提醒的是:
  • JSON列存储的数据要么是NULL,要么必须是JSON格式数据,否则会报错。
  • JSON数据类型是没有默认值的(声明时"DEFAULT NULL")。

 
5、MySQL关于JSON的内置函数

MySQL关于JSON数据格式的操作提供了很多高效率的内置函数,我们可以从MySQL官网上找到很详细的介绍和使用说明。 
从JSON功能介绍的主页也可以看到,这些内置函数支持我们创建、查找、替换和返回值等相关的操作,像我们替换指定内容的操作就可以使用JSON_REPLACE()这个函数,不过最后实现通过纯SQL语句执行最终的内容替换,你还需要通过执行UPDATE语句,比如:
UPDATE tab_base_info 
SET content = json_replace(content, '$.author', "tom")
WHERE id = 1;

 其中“$.***”表示找到JSON内容中匹配的修改字段。

更多关于这些内置函数的用法,大家都可以到官网(链接请查看本文参考资料)的文档上去查阅,写的十分详细而且还有举例。 
官方文档:https://dev.mysql.com/doc/refman/5.7/en/json.html
 

MySQL 8.0 正式版发布:比 MySQL 5.7 快 2 倍

数据库zkbhj 发表了文章 • 0 个评论 • 894 次浏览 • 2018-05-25 10:13 • 来自相关话题

MySQL 8.0 正式版 8.0.11 已发布,官方表示 MySQL 8 要比 MySQL 5.7 快 2 倍,还带来了大量的改进和更快的性能!

注意:从 MySQL 5.7 升级到 MySQL 8.0 仅支持通过使用 in-place 方式进行升级,并且不支持从 MySQL 8.0 降级到 MySQL 5.7(或从某个 MySQL 8.0 版本降级到任意一个更早的 MySQL 8.0 版本)。唯一受支持的替代方案是在升级之前对数据进行备份。

 
下面简要介绍 MySQL 8 中值得关注的新特性和改进。

1. 性能:MySQL 8.0 的速度要比 MySQL 5.7 快 2 倍。MySQL 8.0 在以下方面带来了更好的性能:读/写工作负载、IO 密集型工作负载、以及高竞争("hot spot"热点竞争问题)工作负载。

2. NoSQL:MySQL 从 5.7 版本开始提供 NoSQL 存储功能,目前在 8.0 版本中这部分功能也得到了更大的改进。该项功能消除了对独立的 NoSQL 文档数据库的需求,而 MySQL 文档存储也为 schema-less 模式的 JSON 文档提供了多文档事务支持和完整的 ACID 合规性。

3. 窗口函数(Window Functions):从 MySQL 8.0 开始,新增了一个叫窗口函数的概念,它可以用来实现若干新的查询方式。窗口函数与 SUM()、COUNT() 这种集合函数类似,但它不会将多行查询结果合并为一行,而是将结果放回多行当中。即窗口函数不需要 GROUP BY。

4. 隐藏索引:在 MySQL 8.0 中,索引可以被“隐藏”和“显示”。当对索引进行隐藏时,它不会被查询优化器所使用。我们可以使用这个特性用于性能调试,例如我们先隐藏一个索引,然后观察其对数据库的影响。如果数据库性能有所下降,说明这个索引是有用的,然后将其“恢复显示”即可;如果数据库性能看不出变化,说明这个索引是多余的,可以考虑删掉。

5. 降序索引:MySQL 8.0 为索引提供按降序方式进行排序的支持,在这种索引中的值也会按降序的方式进行排序。

6. 通用表表达式(Common Table Expressions CTE):在复杂的查询中使用嵌入式表时,使用 CTE 使得查询语句更清晰。

7. UTF-8 编码:从 MySQL 8 开始,使用 utf8mb4 作为 MySQL 的默认字符集。

8. JSON:MySQL 8 大幅改进了对 JSON 的支持,添加了基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数,以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数。

9. 可靠性:InnoDB 现在支持表 DDL 的原子性,也就是 InnoDB 表上的 DDL 也可以实现事务完整性,要么失败回滚,要么成功提交,不至于出现 DDL 时部分成功的问题,此外还支持 crash-safe 特性,元数据存储在单个事务数据字典中。

10. 高可用性(High Availability):InnoDB 集群为您的数据库提供集成的原生 HA 解决方案。

11. 安全性:对 OpenSSL 的改进、新的默认身份验证、SQL 角色、密码强度、授权。

    MySQL 8.0.11更改(2018-04-19,可用性)

仅使用就地升级方法支持从MySQL 5.7升级到MySQL 8.0。

不支持从MySQL 8.0降级到MySQL 5.7(或从MySQL 8.0发布到以前的MySQL 8.0发行版)。唯一受支持的替代方法是在升级之前恢复备份 。

 

详细更新说明:https://dev.mysql.com/doc/reln ... .html  
官方发布说明:https://blogs.oracle.com/mysql ... ql-80  
MySQL 8 正式版的新增功能:https://mysqlserverteam.com/wh ... able/

下载地址

Windows (x86, 64-bit), ZIP Archive(mysql-8.0.11-winx64.zip) 183.3M
https://dev.mysql.com/downloads/file/?id=476233

Windows (x86, 64-bit), ZIP Archive(mysql-8.0.11-winx64-debug-test.zip) 230.5M
Debug Binaries & Test Suite
https://dev.mysql.com/downloads/file/?id=476234

其他版本下载地址 >>> https://dev.mysql.com/downloads/mysql/8.0.html

简介:

MySQL是一个开放源码的小型关联式数据库管理系统,开发者为瑞典MySQL AB公司。目前MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。

MySQL的特性

使用C和C++编写,并使用了多种编译器进行测试,保证源代码的可移植性。

支持AIX、BSDi、FreeBSD、HP-UX、Linux、Mac OS、Novell Netware、NetBSD、OpenBSD、OS/2 Wrap、Solaris、SunOS、Windows等多种操作系统。

为多种编程语言提供了API。这些编程语言包括C、C++、C#、Delphi、Eiffel、Java、Perl、PHP、Python、Ruby和Tcl等。

支持多线程,充分利用CPU资源,支持多用户。

优化的SQL查询算法,有效地提高查询速度。

既能够作为一个单独的应用程序应用在客户端服务器网络环境中,也能够作为一个库而嵌入到其他的软件中。

提供多语言支持,常见的编码如中文的GB 2312、BIG5,日文的Shift_JIS等都可以用作数据表名和数据列名。

提供TCP/IP、ODBC和JDBC等多种数据库连接途径。

提供用于管理、检查、优化数据库操作的管理工具。

可以处理拥有上千万条记录的大型数据库。

中文 MySQL 文档:http://tool.oschina.net/apidoc ... .1-zh

英文 MySQL 文档:http://tool.oschina.net/apidoc ... .5-en 查看全部
MySQL 8.0 正式版 8.0.11 已发布,官方表示 MySQL 8 要比 MySQL 5.7 快 2 倍,还带来了大量的改进和更快的性能!


注意:从 MySQL 5.7 升级到 MySQL 8.0 仅支持通过使用 in-place 方式进行升级,并且不支持从 MySQL 8.0 降级到 MySQL 5.7(或从某个 MySQL 8.0 版本降级到任意一个更早的 MySQL 8.0 版本)。唯一受支持的替代方案是在升级之前对数据进行备份。


 
下面简要介绍 MySQL 8 中值得关注的新特性和改进。

1. 性能:MySQL 8.0 的速度要比 MySQL 5.7 快 2 倍。MySQL 8.0 在以下方面带来了更好的性能:读/写工作负载、IO 密集型工作负载、以及高竞争("hot spot"热点竞争问题)工作负载。

2. NoSQL:MySQL 从 5.7 版本开始提供 NoSQL 存储功能,目前在 8.0 版本中这部分功能也得到了更大的改进。该项功能消除了对独立的 NoSQL 文档数据库的需求,而 MySQL 文档存储也为 schema-less 模式的 JSON 文档提供了多文档事务支持和完整的 ACID 合规性。

3. 窗口函数(Window Functions):从 MySQL 8.0 开始,新增了一个叫窗口函数的概念,它可以用来实现若干新的查询方式。窗口函数与 SUM()、COUNT() 这种集合函数类似,但它不会将多行查询结果合并为一行,而是将结果放回多行当中。即窗口函数不需要 GROUP BY。

4. 隐藏索引:在 MySQL 8.0 中,索引可以被“隐藏”和“显示”。当对索引进行隐藏时,它不会被查询优化器所使用。我们可以使用这个特性用于性能调试,例如我们先隐藏一个索引,然后观察其对数据库的影响。如果数据库性能有所下降,说明这个索引是有用的,然后将其“恢复显示”即可;如果数据库性能看不出变化,说明这个索引是多余的,可以考虑删掉。

5. 降序索引:MySQL 8.0 为索引提供按降序方式进行排序的支持,在这种索引中的值也会按降序的方式进行排序。

6. 通用表表达式(Common Table Expressions CTE):在复杂的查询中使用嵌入式表时,使用 CTE 使得查询语句更清晰。

7. UTF-8 编码:从 MySQL 8 开始,使用 utf8mb4 作为 MySQL 的默认字符集。

8. JSON:MySQL 8 大幅改进了对 JSON 的支持,添加了基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数,以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数。

9. 可靠性:InnoDB 现在支持表 DDL 的原子性,也就是 InnoDB 表上的 DDL 也可以实现事务完整性,要么失败回滚,要么成功提交,不至于出现 DDL 时部分成功的问题,此外还支持 crash-safe 特性,元数据存储在单个事务数据字典中。

10. 高可用性(High Availability):InnoDB 集群为您的数据库提供集成的原生 HA 解决方案。

11. 安全性:对 OpenSSL 的改进、新的默认身份验证、SQL 角色、密码强度、授权。

    MySQL 8.0.11更改(2018-04-19,可用性)

仅使用就地升级方法支持从MySQL 5.7升级到MySQL 8.0。

不支持从MySQL 8.0降级到MySQL 5.7(或从MySQL 8.0发布到以前的MySQL 8.0发行版)。唯一受支持的替代方法是在升级之前恢复备份 。

 

详细更新说明:https://dev.mysql.com/doc/reln ... .html  
官方发布说明:https://blogs.oracle.com/mysql ... ql-80  
MySQL 8 正式版的新增功能:https://mysqlserverteam.com/wh ... able/

下载地址

Windows (x86, 64-bit), ZIP Archive(mysql-8.0.11-winx64.zip) 183.3M
https://dev.mysql.com/downloads/file/?id=476233

Windows (x86, 64-bit), ZIP Archive(mysql-8.0.11-winx64-debug-test.zip) 230.5M
Debug Binaries & Test Suite
https://dev.mysql.com/downloads/file/?id=476234

其他版本下载地址 >>> https://dev.mysql.com/downloads/mysql/8.0.html

简介:

MySQL是一个开放源码的小型关联式数据库管理系统,开发者为瑞典MySQL AB公司。目前MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。

MySQL的特性

使用C和C++编写,并使用了多种编译器进行测试,保证源代码的可移植性。

支持AIX、BSDi、FreeBSD、HP-UX、Linux、Mac OS、Novell Netware、NetBSD、OpenBSD、OS/2 Wrap、Solaris、SunOS、Windows等多种操作系统。

为多种编程语言提供了API。这些编程语言包括C、C++、C#、Delphi、Eiffel、Java、Perl、PHP、Python、Ruby和Tcl等。

支持多线程,充分利用CPU资源,支持多用户。

优化的SQL查询算法,有效地提高查询速度。

既能够作为一个单独的应用程序应用在客户端服务器网络环境中,也能够作为一个库而嵌入到其他的软件中。

提供多语言支持,常见的编码如中文的GB 2312、BIG5,日文的Shift_JIS等都可以用作数据表名和数据列名。

提供TCP/IP、ODBC和JDBC等多种数据库连接途径。

提供用于管理、检查、优化数据库操作的管理工具。

可以处理拥有上千万条记录的大型数据库。

中文 MySQL 文档:http://tool.oschina.net/apidoc ... .1-zh

英文 MySQL 文档:http://tool.oschina.net/apidoc ... .5-en

MySQL实践:数据库建表规范整理

数据库zkbhj 发表了文章 • 0 个评论 • 917 次浏览 • 2018-05-16 09:41 • 来自相关话题

设计表:

【强制】创建表时必须显式指定字符集为utf8或utf8mb4。
【强制】表和列的名称必须控制在32个字符以内,表名只能使用字母、数字和下划线,一律小写,不能使用mysql的关键字。
【强制】创建表时必须显式指定表存储引擎类型,如无特殊需求,一律为InnoDB。当需要使用除InnoDB/MyISAM/Memory以外的存储引擎时,必须通过DBA审核才能在生产环境中使用。因为Innodb表支持事务、行锁、宕机恢复、MVCC等关系型数据库重要特性,为业界使用最多的MySQL存储引擎。而这是其他大多数存储引擎不具备的,因此首推InnoDB。
【强制】建表必须有comment。
【建议】建表时关于主键:强制要求主键为id,类型为int或bigint,且为auto_increment。
【建议】表中所有字段必须都是NOT NULL属性,业务可以根据需要定义DEFAULT值。 因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。
【强制】对于超过100W行的大表进行alter table,必须经过DBA审核,并在业务低峰期执行。因为alter table会产生表锁,期间阻塞对于该表的所有写入,对于业务可能会产生极大影响。
【强制】数据库本身库、表、列所有字符集必须保持一致,为utf8或utf8mb4。
【强制】表名要求模块名强相关,如师资系统采用”sz”作为前缀,渠道系统采用”qd”作为前缀等。
【强制】标识表里每一行主体的字段不要设为主键,建议设为其他字段如user_id,order_id等,并建立unique key索引(可参考cdb.teacher表设计)。因为如果设为主键且主键值为随机插入,则会导致innodb内部page分裂和大量随机I/O,性能下降。
【建议】核心表(如用户表,金钱相关的表)必须有行数据的创建时间字段create_time和最后更新时间字段update_time,便于查问题。我们这边建议所有表都加这俩字段
【建议】建议对表里的blob、text等大字段,垂直拆分到其他表里,仅在需要读这些对象的时候才去select。
【强制】中间表用于保留中间结果集,名称必须以“tmp_”开头。备份表用于备份或抓取源表快照,名称必须以“bak_”开头。 中间表和备份表定期清理
【建议】反范式设计:把经常需要join查询的字段,在其他表里冗余一份。如user_name属性在user_account,user_login_log等表里冗余一份,减少join查询。
【建议】字段一定要写好备注。

列数据类型优化
1. 【建议】表中的自增列(auto_increment属性),推荐使用bigint类型。
因为无符号int存储范围为-2147483648~2147483647(大约21亿左右),溢出后会导致报错。
2. 【建议】业务中选择性很少的状态status、类型type等字段推荐使用tinytint或者smallint类型节省存储空间。
3. 【建议】不推荐使用enum,set
因为它们浪费空间,且枚举值写死了,变更不方便。推荐使用tinyint或smallint
4. 【建议】不推荐使用blob,text等类型
它们都比较浪费硬盘和内存空间。在加载表数据时,会读取大字段到内存里从而浪费内存空间,影响系统性能。建议和PM、RD沟通,是否真的需要这么大字段?
Innodb中当一行记录超过8098字节时,会将该记录中选取最长的一个字段将其768字节放在原始page里,该字段余下内容放在overflow-page里。不幸的是在compact行格式下,原始page和overflow-page都会加载。
5. 【建议】存储金钱的字段,建议用int,程序端乘以100和除以100进行存取。因为int占用4字节,而double占用8字节,空间浪费。
6. 【建议】文本数据尽量用varchar存储
因为varchar是变长存储,比char更省空间。MySQL server层规定一行所有文本最多存65535字节,因此在utf8字符集下最多存21844个字符,超过会自动转换为mediumtext字段。而text在utf8字符集下最多存21844个字符,mediumtext最多存2^24/3个字符,longtext最多存2^32个字符。
一般建议用varchar类型,字符数不要超过2700
7. 【建议】时间类型尽量选取timestamp
因为datetime占用8字节,timestamp仅占用4字节,但是范围为1970-01-01 00:00:01到2038-01-01 00:00:00。
更为高阶的方法,选用int来存储时间,使用SQL函数unix_timestamp()和from_unixtime()来进行转换。
8.【建议】手机号用varchar(20)
理由:涉及到区号或者国家代号,可能出现+-();手机号不会去做数学运算;varchar可    以支持模糊查询,例如:like“138%”
 
索引设计
1. 【强制】InnoDB表必须主键为id int/bigint auto_increment,且主键值禁止被更新。
2. 【建议】主键的名称以“pk_”开头,唯一键以“uk_”或“uq_”开头,普通索引以“idx_”开头,一律使用小写格式,以表名/字段的名称或缩写作为后缀。
3. 【强制】InnoDB和MyISAM存储引擎表,索引类型必须为BTREE;MEMORY表可以根据需要选择HASH或者BTREE类型索引。
4. 【强制】单个索引中每个索引记录的长度不能超过64KB
5. 【建议】单个表上的索引个数不能超过7个
6. 【建议】在建立索引时,多考虑建立联合索引,并把区分度最高的字段放在最前面。如列userid的区分度可由select count(distinct userid)/select count计算出来。
7. 【建议】在多表join的SQL里,保证被驱动表的连接列上有索引,这样join执行效率最高。
8. 【建议】建表或加索引时,保证表里互相不存在冗余索引。
对于MySQL来说,如果表里已经存在key(a,b),则key(a)为冗余索引,需要删除。
9. 单索引字段数不允许超过5个。
 
线上禁止使用的SQL语句
【高危】禁用update|delete t1 … where a=XX limit XX; 这种带limit的更新语句。因为会导致主从不一致,导致数据错乱。建议加上order by PK【高危】禁止使用关联子查询,如update t1 set … where name in(select name from user where…);效率极其低下。
【强制】禁用procedure、function、trigger、views、event、外键约束。因为他们消耗数据库资源,降低数据库集群可扩展性。推荐都在程序端实现。【强制】禁用insert into …on duplicate key update…(如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE;如果不会导致唯一值列重复的问题,则插入新行。)在高并发环境下,会造成主从不一致。【强制】禁止联表更新语句,如update t1,t2 where t1.id=t2.id…
【强制】禁止单独使用负向查询NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等,会导致全表扫描。如SELECT oid FROM t_order WHERE status != 1 不行,SELECT oid FROM t_order WHERE uid=123 AND status != 1 两个以上的where条件可以有负向查询
【强制】insert语句指定具体字段名称,不要写成insert into t1 values(…),因为id很多时候无法指定,字段顺序也容易乱。

【强制】除静态表(字段有固定长度)或小表(100行以内),DML语句必须有where条件,且使用索引查找。
【强制】禁止在业务的更新类SQL语句中使用join,比如update t1 join t2… 查看全部
设计表:

【强制】创建表时必须显式指定字符集为utf8或utf8mb4。
【强制】表和列的名称必须控制在32个字符以内,表名只能使用字母、数字和下划线,一律小写,不能使用mysql的关键字。
【强制】创建表时必须显式指定表存储引擎类型,如无特殊需求,一律为InnoDB。当需要使用除InnoDB/MyISAM/Memory以外的存储引擎时,必须通过DBA审核才能在生产环境中使用。因为Innodb表支持事务、行锁、宕机恢复、MVCC等关系型数据库重要特性,为业界使用最多的MySQL存储引擎。而这是其他大多数存储引擎不具备的,因此首推InnoDB。
【强制】建表必须有comment。
【建议】建表时关于主键:强制要求主键为id,类型为int或bigint,且为auto_increment。
【建议】表中所有字段必须都是NOT NULL属性,业务可以根据需要定义DEFAULT值。 因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。
【强制】对于超过100W行的大表进行alter table,必须经过DBA审核,并在业务低峰期执行。因为alter table会产生表锁,期间阻塞对于该表的所有写入,对于业务可能会产生极大影响。
【强制】数据库本身库、表、列所有字符集必须保持一致,为utf8或utf8mb4。
【强制】表名要求模块名强相关,如师资系统采用”sz”作为前缀,渠道系统采用”qd”作为前缀等。
【强制】标识表里每一行主体的字段不要设为主键,建议设为其他字段如user_id,order_id等,并建立unique key索引(可参考cdb.teacher表设计)。因为如果设为主键且主键值为随机插入,则会导致innodb内部page分裂和大量随机I/O,性能下降。
【建议】核心表(如用户表,金钱相关的表)必须有行数据的创建时间字段create_time和最后更新时间字段update_time,便于查问题。我们这边建议所有表都加这俩字段
【建议】建议对表里的blob、text等大字段,垂直拆分到其他表里,仅在需要读这些对象的时候才去select。
【强制】中间表用于保留中间结果集,名称必须以“tmp_”开头。备份表用于备份或抓取源表快照,名称必须以“bak_”开头。 中间表和备份表定期清理
【建议】反范式设计:把经常需要join查询的字段,在其他表里冗余一份。如user_name属性在user_account,user_login_log等表里冗余一份,减少join查询。
【建议】字段一定要写好备注。

列数据类型优化
1. 【建议】表中的自增列(auto_increment属性),推荐使用bigint类型。
因为无符号int存储范围为-2147483648~2147483647(大约21亿左右),溢出后会导致报错。
2. 【建议】业务中选择性很少的状态status、类型type等字段推荐使用tinytint或者smallint类型节省存储空间。
3. 【建议】不推荐使用enum,set
因为它们浪费空间,且枚举值写死了,变更不方便。推荐使用tinyint或smallint
4. 【建议】不推荐使用blob,text等类型
它们都比较浪费硬盘和内存空间。在加载表数据时,会读取大字段到内存里从而浪费内存空间,影响系统性能。建议和PM、RD沟通,是否真的需要这么大字段?
Innodb中当一行记录超过8098字节时,会将该记录中选取最长的一个字段将其768字节放在原始page里,该字段余下内容放在overflow-page里。不幸的是在compact行格式下,原始page和overflow-page都会加载。
5. 【建议】存储金钱的字段,建议用int,程序端乘以100和除以100进行存取。因为int占用4字节,而double占用8字节,空间浪费。
6. 【建议】文本数据尽量用varchar存储
因为varchar是变长存储,比char更省空间。MySQL server层规定一行所有文本最多存65535字节,因此在utf8字符集下最多存21844个字符,超过会自动转换为mediumtext字段。而text在utf8字符集下最多存21844个字符,mediumtext最多存2^24/3个字符,longtext最多存2^32个字符。
一般建议用varchar类型,字符数不要超过2700
7. 【建议】时间类型尽量选取timestamp
因为datetime占用8字节,timestamp仅占用4字节,但是范围为1970-01-01 00:00:01到2038-01-01 00:00:00。
更为高阶的方法,选用int来存储时间,使用SQL函数unix_timestamp()和from_unixtime()来进行转换。
8.【建议】手机号用varchar(20)
理由:涉及到区号或者国家代号,可能出现+-();手机号不会去做数学运算;varchar可    以支持模糊查询,例如:like“138%”
 
索引设计
1. 【强制】InnoDB表必须主键为id int/bigint auto_increment,且主键值禁止被更新。
2. 【建议】主键的名称以“pk_”开头,唯一键以“uk_”或“uq_”开头,普通索引以“idx_”开头,一律使用小写格式,以表名/字段的名称或缩写作为后缀。
3. 【强制】InnoDB和MyISAM存储引擎表,索引类型必须为BTREE;MEMORY表可以根据需要选择HASH或者BTREE类型索引。
4. 【强制】单个索引中每个索引记录的长度不能超过64KB
5. 【建议】单个表上的索引个数不能超过7个
6. 【建议】在建立索引时,多考虑建立联合索引,并把区分度最高的字段放在最前面。如列userid的区分度可由select count(distinct userid)/select count计算出来。
7. 【建议】在多表join的SQL里,保证被驱动表的连接列上有索引,这样join执行效率最高。
8. 【建议】建表或加索引时,保证表里互相不存在冗余索引。
对于MySQL来说,如果表里已经存在key(a,b),则key(a)为冗余索引,需要删除。
9. 单索引字段数不允许超过5个。
 
线上禁止使用的SQL语句
【高危】禁用update|delete t1 … where a=XX limit XX; 这种带limit的更新语句。因为会导致主从不一致,导致数据错乱。建议加上order by PK【高危】禁止使用关联子查询,如update t1 set … where name in(select name from user where…);效率极其低下。
【强制】禁用procedure、function、trigger、views、event、外键约束。因为他们消耗数据库资源,降低数据库集群可扩展性。推荐都在程序端实现。【强制】禁用insert into …on duplicate key update…(如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE;如果不会导致唯一值列重复的问题,则插入新行。)在高并发环境下,会造成主从不一致。【强制】禁止联表更新语句,如update t1,t2 where t1.id=t2.id…
【强制】禁止单独使用负向查询NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等,会导致全表扫描。如SELECT oid FROM t_order WHERE status != 1 不行,SELECT oid FROM t_order WHERE uid=123 AND status != 1 两个以上的where条件可以有负向查询
【强制】insert语句指定具体字段名称,不要写成insert into t1 values(…),因为id很多时候无法指定,字段顺序也容易乱。

【强制】除静态表(字段有固定长度)或小表(100行以内),DML语句必须有where条件,且使用索引查找。
【强制】禁止在业务的更新类SQL语句中使用join,比如update t1 join t2…

数据库操作中TRUNCATE的命令用法

数据库zkbhj 发表了文章 • 0 个评论 • 1039 次浏览 • 2018-03-26 11:31 • 来自相关话题

删除表中的所有行,而不记录单个行删除操作。

语法TRUNCATE TABLE name

参数

name

是要截断的表的名称或要删除其全部行的表的名称。

注释

TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。

DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。

TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。如果要删除表定义及其数据,请使用 DROP TABLE 语句。

对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE TABLE,而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器。

TRUNCATE TABLE 不能用于参与了索引视图的表。

示例

下例删除 authors 表中的所有数据。
 
TRUNCATE TABLE authors
 
权限

TRUNCATE TABLE 权限默认授予表所有者、sysadmin 固定服务器角色成员、db_owner 和 db_ddladmin 固定数据库角色成员且不可转让。 查看全部
删除表中的所有行,而不记录单个行删除操作。

语法
TRUNCATE TABLE name


参数

name

是要截断的表的名称或要删除其全部行的表的名称。

注释

TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。

DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。

TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。如果要删除表定义及其数据,请使用 DROP TABLE 语句。

对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE TABLE,而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器。

TRUNCATE TABLE 不能用于参与了索引视图的表。

示例

下例删除 authors 表中的所有数据。
 
TRUNCATE TABLE authors
 
权限

TRUNCATE TABLE 权限默认授予表所有者、sysadmin 固定服务器角色成员、db_owner 和 db_ddladmin 固定数据库角色成员且不可转让。

理解Redis的几种数据结构

工具软件zkbhj 发表了文章 • 0 个评论 • 867 次浏览 • 2018-01-29 14:31 • 来自相关话题

一、Redis数据库的优势

Redis是一个先进的Key-Value键值存储数据库,通常作为数据结构服务器。 

支持strings, hashes, lists, sets, sorted sets, bitmaps 和hyperloglogs

二、Redis数据结构

1、string - 字符串
Redis的字符串为SDS(Simple Dynamic String)可以存储任何东西,最大长度可达515兆。
#redis-cli.exe
127.0.0.1:6379> set name 'cbb'
OK
127.0.0.1:6379> get name
"cbb"SDS的数据结构如下:
struct sdshdr {
// 记录 buf 数组中已使用字节的数量
// 等于 SDS 所保存字符串的长度
int len;
// 记录 buf 数组中未使用字节的数量
int free;
// 字节数组,用于保存字符串
char buf[];
};常用命令:set, get
 
2,Hash - 哈希值

Redis的哈希值是字符串字段和字符串值之间的映射,所有它们被用来表示对象。
#redis-cli.exe
127.0.0.1:6379> HMSET user:1 username ccc password 123 age 20
OK
127.0.0.1:6379> HGETALL user:1 //取所有key
1> "username"
2> "ccc"
3> "passwrod"
4> "123"
5> "age"
6> "20"

127.0.0.1:6379> hget user:1 username //取一个key
"ccc"上面的Hash数据类型,用于存储用户的基本信息,user:1是键。

hash解决了用户信息对象的存储:如用户ID为key,value为姓名name,年龄age,生日birthday等。 
用普通的key/value结构存储: a,set u001 “张三, 18, 20010101”
这种方式的缺点是增加了序列化/反序列化的开销,并且在需要修改其中一项信息时,需要把整个对象取回,并且修改操作需要对并发进行保护,引入CAS等复杂问题。b,mset user:001:name “李三” user:001:age 18 user:001:birthday “20010101” 这种方法是用户信息对象有多少个成员就存成多少个key-value对儿,虽然省去了序列化开销和并发问题,但是用户ID为重复存储,如果存在大量这样的数据,内存浪费严重。

常用命令:hmset, hset, hget, hgetall 
3,List - 列表

列表是简单的字符串列表,排序插入顺序。可以添加元素到Redis列表的头部或尾部。#redis-cli.exe
127.0.0.1:6379> lpush tutor redis
<integer> 1
127.0.0.1:6379> lpush tutor mongodb
<integer> 2
127.0.0.1:6379> lpush tutor rabitmq
<integer> 3
127.0.0.1:6379> lrange tutor 0 10
1> "rabitmq"
2> "mongodb"
3> "redis"节点的定义:
typedef struct listNode {
// 前置节点
struct listNode *prev;
// 后置节点
struct listNode *next;
// 节点的值
void *value;
} listNode;List的定义
typedef struct list {
// 表头节点
listNode *head;
// 表尾节点
listNode *tail;
// 链表所包含的节点数量
unsigned long len;
// 节点值复制函数
void *(*dup)(void *ptr);
// 节点值释放函数
void (*free)(void *ptr);
// 节点值对比函数
int (*match)(void *ptr, void *key);
} list;补充:
 
链表被广泛用于实现 Redis 的各种功能, 比如列表键, 发布与订阅, 慢查询, 监视器, 等等。因为链表表头节点的前置节点和表尾节点的后置节点都指向 NULL , 所以 Redis 的链表实现是无环链表。通过为链表设置不同的类型特定函数, Redis 的链表可以用于保存各种不同类型的值。

常用命令:lpush, rpush, lpop, rpop, lrange等 
4,Set - 集合

Set是字符串的无序集合。在Redis中可以添加、删除和测试值是否存在。
#redis-cli.exe
127.0.0.1:6379> sadd total 123
<integer> 1
127.0.0.1:6379> sadd total 234
<integer> 1
127.0.0.1:6379> sadd total 345
<integer> 1
127.0.0.1:6379> smembers total
1> "123"
2> "234"
3> "345"常用命令:sadd, srem, spop, sdiff, smembers, sunion等
 
5,Sort Set - 有序集合

与Set类似,字符串不重复,但其是有序的 
常用命令:zadd, zrange,zrem,zcard等
 
6,Pub/Sub - 消息订阅

发布(Publish)和订阅(Subscribe)

~两客户端之间~ 
client1: 




client2: 






当一个key值上进行了消息发布后,所有订阅它的客户端都会收到相应的消息。 
这一功能最明显的用法是作为实时消息系统,比如普通的即时聊天,群聊等功能。 查看全部
一、Redis数据库的优势

Redis是一个先进的Key-Value键值存储数据库,通常作为数据结构服务器。 


支持strings, hashes, lists, sets, sorted sets, bitmaps 和hyperloglogs


二、Redis数据结构

1、string - 字符串
Redis的字符串为SDS(Simple Dynamic String)可以存储任何东西,最大长度可达515兆。
#redis-cli.exe
127.0.0.1:6379> set name 'cbb'
OK
127.0.0.1:6379> get name
"cbb"
SDS的数据结构如下:
struct sdshdr {
// 记录 buf 数组中已使用字节的数量
// 等于 SDS 所保存字符串的长度
int len;
// 记录 buf 数组中未使用字节的数量
int free;
// 字节数组,用于保存字符串
char buf[];
};
常用命令:set, get
 
2,Hash - 哈希值

Redis的哈希值是字符串字段和字符串值之间的映射,所有它们被用来表示对象。
#redis-cli.exe
127.0.0.1:6379> HMSET user:1 username ccc password 123 age 20
OK
127.0.0.1:6379> HGETALL user:1 //取所有key
1> "username"
2> "ccc"
3> "passwrod"
4> "123"
5> "age"
6> "20"

127.0.0.1:6379> hget user:1 username //取一个key
"ccc"
上面的Hash数据类型,用于存储用户的基本信息,user:1是键。

hash解决了用户信息对象的存储:如用户ID为key,value为姓名name,年龄age,生日birthday等。 
用普通的key/value结构存储: 
a,set u001 “张三, 18, 20010101” 

这种方式的缺点是增加了序列化/反序列化的开销,并且在需要修改其中一项信息时,需要把整个对象取回,并且修改操作需要对并发进行保护,引入CAS等复杂问题。
b,mset user:001:name “李三” user:001:age 18 user:001:birthday “20010101” 
这种方法是用户信息对象有多少个成员就存成多少个key-value对儿,虽然省去了序列化开销和并发问题,但是用户ID为重复存储,如果存在大量这样的数据,内存浪费严重。

常用命令:hmset, hset, hget, hgetall 
3,List - 列表

列表是简单的字符串列表,排序插入顺序。可以添加元素到Redis列表的头部或尾部。
#redis-cli.exe
127.0.0.1:6379> lpush tutor redis
<integer> 1
127.0.0.1:6379> lpush tutor mongodb
<integer> 2
127.0.0.1:6379> lpush tutor rabitmq
<integer> 3
127.0.0.1:6379> lrange tutor 0 10
1> "rabitmq"
2> "mongodb"
3> "redis"
节点的定义:
typedef struct listNode {
// 前置节点
struct listNode *prev;
// 后置节点
struct listNode *next;
// 节点的值
void *value;
} listNode;
List的定义
typedef struct list {
// 表头节点
listNode *head;
// 表尾节点
listNode *tail;
// 链表所包含的节点数量
unsigned long len;
// 节点值复制函数
void *(*dup)(void *ptr);
// 节点值释放函数
void (*free)(void *ptr);
// 节点值对比函数
int (*match)(void *ptr, void *key);
} list;
补充:
 
  • 链表被广泛用于实现 Redis 的各种功能, 比如列表键, 发布与订阅, 慢查询, 监视器, 等等。
  • 因为链表表头节点的前置节点和表尾节点的后置节点都指向 NULL , 所以 Redis 的链表实现是无环链表。
  • 通过为链表设置不同的类型特定函数, Redis 的链表可以用于保存各种不同类型的值。


常用命令:lpush, rpush, lpop, rpop, lrange等 
4,Set - 集合

Set是字符串的无序集合。在Redis中可以添加、删除和测试值是否存在。
#redis-cli.exe
127.0.0.1:6379> sadd total 123
<integer> 1
127.0.0.1:6379> sadd total 234
<integer> 1
127.0.0.1:6379> sadd total 345
<integer> 1
127.0.0.1:6379> smembers total
1> "123"
2> "234"
3> "345"
常用命令:sadd, srem, spop, sdiff, smembers, sunion等
 
5,Sort Set - 有序集合

与Set类似,字符串不重复,但其是有序的 
常用命令:zadd, zrange,zrem,zcard等
 
6,Pub/Sub - 消息订阅

发布(Publish)和订阅(Subscribe)

~两客户端之间~ 
client1: 
QQ截图20180129142948.jpg

client2: 

QQ截图20180129143032.jpg


当一个key值上进行了消息发布后,所有订阅它的客户端都会收到相应的消息。 
这一功能最明显的用法是作为实时消息系统,比如普通的即时聊天,群聊等功能。

#每天进步一点点#1107MySQL:索引工作原理

服务器zkbhj 发表了文章 • 0 个评论 • 659 次浏览 • 2017-11-07 23:02 • 来自相关话题

为什么需要索引(Why is it needed)?
 
当数据保存在磁盘类存储介质上时,它是作为数据块存放。这些数据块是被当作一个整体来访问的,这样可以保证操作的原子性。硬盘数据块存储结构类似于链表,都包含数据部分,以及一个指向下一个节点(或数据块)的指针,不需要连续存储。

记录集只能在某个关键字段上进行排序,所以如果需要在一个无序字段上进行搜索,就要执行一个线性搜索(Linear Search)的过程,平均需要访问N/2的数据块,N是表所占据的数据块数目。如果这个字段是一个非主键字段(也就是说,不包含唯一的访问入口),那么需要在N个数据块上搜索整个表格空间。

但是对于一个有序字段,可以运用二分查找(Binary Search),这样只要访问log2 (N)的数据块。这就是为什么性能能得到本质上的提高。 
什么是索引(What is indexing)?
 
索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引,将创建另外一个数据结构,包含字段数值以及指向相关记录的指针,然后对这个索引结构进行排序,允许在该数据上进行二分法排序。

副作用是索引需要额外的磁盘空间,对于MyISAM引擎而言,这些索引是被统一保存在一张表中的,这个文件将很快到达底层文件系统所能够支持的大小限制,如果很多字段都建立了索引的话。
 
索引如何工作(How does it work?)
 
首先,我们建立一个示范数据库表:

字段名       数据类型      大小
id (Primary key) Unsigned INT   4 bytes
firstName        Char(50)       50 bytes
lastName         Char(50)       50 bytes
emailAddress     Char(100)      100 bytes
注意:使用char是为了指定准确的磁盘占用大小。这个示范数据库包含500万行,而且没有索引。我们将分析一些查询语句的性能,一个是使用主键id(有序)查询,一个是使用firstName(非关键无序字段)。

例1
 
我们的示范数据库有r=5,000,000条记录,每条记录长度R=204字节而且使用MyISAM引擎存储(默认数据块大小为B=1024字节),这张表的块因子(blocking factor)会是bfr = (B/R) = 1024/204 = 5 条记录每磁盘数据块。保存这张表所需要的磁盘块为N = (r/bfr) = 5000000/5 = 1,000,000 blocks。

在id字段上的线性搜索平均需要N/2 = 500,000块访问来找到一条记录假设id字段是查询关键值,不过既然id字段是有序的,可以执行一个二分查询,这样平均只需要访问log2 (1000000) = 19.93 = 20 个数据块。我们马上就看到了极大的提高。

现在firstName字段既不是有序的,无法执行二分搜索,数值也不具有唯一性,所以对这张表的查找必须到最后一个记录即全表扫描N = 1,000,000个数据块访问。这就是索引用来改进的地方。

假如索引记录只包含一个索引列以及一个指向原记录数据的指针,那么它显而易见会比原记录(多列)要小。所以索引本身所需要的磁盘块要更少,扫描数目也少。firstName索引表结构如下:

Field name       Data type      Size on disk
firstName        Char(50)       50 bytes
(record pointer) Special        4 bytes
注意: MySQL里的指针按表大小的不同分别可能是 2, 3, 4 或 5 个字节。

例2

假设我们的数据库有r = 5,000,000 条记录,建立了一个长R = 54字节的索引,并且使用默认磁盘块大小为1,024字节。那么该索引的块因子为bfr = (B/R) = 1024/54 = 18 条记录每磁盘块。容纳这个索引表总共需要的磁盘块为N = (r/bfr) = 5000000/18 = 277,778 块。

现在使用FirstName字段来进行搜索就可以利用索引来提高性能。这允许使用一个二分查找,平均log2 (277778) = 18.08 -> 19次数据块访问。找到实际记录的地址,这需要进一步的块读取,这样总数达到19 + 1 = 20次数据块访问,这和非索引表的数据块访问次数有天壤之别。


什么时候使用索引(When should it be used?)
 
鉴于创建索引需要额外的磁盘空间(上面的例子需要额外的277778个磁盘块),以及太多的索引会导致文件系统大小限制所产生的问题,所以对哪些字段建立索引,什么情况下使用索引,需要审慎考虑。

由于索引只是用来加速数据查询,那么显然对只是用来输出的字段建立索引会浪费磁盘空间以及发生插入、删除操作时的处理时间,所以这种情况下应该尽量避免。此外鉴于二分搜索的特性,数据的基数或独立性是很重要的。在基数为2的字段上建立索引,将把数据分割一半,而基数为1000则将返回大约1000条记录。低基数的二分查找效率将降低为一个线性排序,而且查询优化器可能会在基数小于记录数某个比例时(如30%)的情况下将避免使用索引而直接查询原表,所以这种情况下的索引浪费了空间。
 
  查看全部
为什么需要索引(Why is it needed)?
 
当数据保存在磁盘类存储介质上时,它是作为数据块存放。这些数据块是被当作一个整体来访问的,这样可以保证操作的原子性。硬盘数据块存储结构类似于链表,都包含数据部分,以及一个指向下一个节点(或数据块)的指针,不需要连续存储。

记录集只能在某个关键字段上进行排序,所以如果需要在一个无序字段上进行搜索,就要执行一个线性搜索(Linear Search)的过程,平均需要访问N/2的数据块,N是表所占据的数据块数目。如果这个字段是一个非主键字段(也就是说,不包含唯一的访问入口),那么需要在N个数据块上搜索整个表格空间。

但是对于一个有序字段,可以运用二分查找(Binary Search),这样只要访问log2 (N)的数据块。这就是为什么性能能得到本质上的提高。 
什么是索引(What is indexing)?
 
索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引,将创建另外一个数据结构,包含字段数值以及指向相关记录的指针,然后对这个索引结构进行排序,允许在该数据上进行二分法排序。

副作用是索引需要额外的磁盘空间,对于MyISAM引擎而言,这些索引是被统一保存在一张表中的,这个文件将很快到达底层文件系统所能够支持的大小限制,如果很多字段都建立了索引的话。
 
索引如何工作(How does it work?)
 
首先,我们建立一个示范数据库表:

字段名       数据类型      大小
id (Primary key) Unsigned INT   4 bytes
firstName        Char(50)       50 bytes
lastName         Char(50)       50 bytes
emailAddress     Char(100)      100 bytes
注意:使用char是为了指定准确的磁盘占用大小。这个示范数据库包含500万行,而且没有索引。我们将分析一些查询语句的性能,一个是使用主键id(有序)查询,一个是使用firstName(非关键无序字段)。

例1
 
我们的示范数据库有r=5,000,000条记录,每条记录长度R=204字节而且使用MyISAM引擎存储(默认数据块大小为B=1024字节),这张表的块因子(blocking factor)会是bfr = (B/R) = 1024/204 = 5 条记录每磁盘数据块。保存这张表所需要的磁盘块为N = (r/bfr) = 5000000/5 = 1,000,000 blocks。

在id字段上的线性搜索平均需要N/2 = 500,000块访问来找到一条记录假设id字段是查询关键值,不过既然id字段是有序的,可以执行一个二分查询,这样平均只需要访问log2 (1000000) = 19.93 = 20 个数据块。我们马上就看到了极大的提高。

现在firstName字段既不是有序的,无法执行二分搜索,数值也不具有唯一性,所以对这张表的查找必须到最后一个记录即全表扫描N = 1,000,000个数据块访问。这就是索引用来改进的地方。

假如索引记录只包含一个索引列以及一个指向原记录数据的指针,那么它显而易见会比原记录(多列)要小。所以索引本身所需要的磁盘块要更少,扫描数目也少。firstName索引表结构如下:

Field name       Data type      Size on disk
firstName        Char(50)       50 bytes
(record pointer) Special        4 bytes
注意: MySQL里的指针按表大小的不同分别可能是 2, 3, 4 或 5 个字节。

例2

假设我们的数据库有r = 5,000,000 条记录,建立了一个长R = 54字节的索引,并且使用默认磁盘块大小为1,024字节。那么该索引的块因子为bfr = (B/R) = 1024/54 = 18 条记录每磁盘块。容纳这个索引表总共需要的磁盘块为N = (r/bfr) = 5000000/18 = 277,778 块。

现在使用FirstName字段来进行搜索就可以利用索引来提高性能。这允许使用一个二分查找,平均log2 (277778) = 18.08 -> 19次数据块访问。找到实际记录的地址,这需要进一步的块读取,这样总数达到19 + 1 = 20次数据块访问,这和非索引表的数据块访问次数有天壤之别。


什么时候使用索引(When should it be used?)
 
鉴于创建索引需要额外的磁盘空间(上面的例子需要额外的277778个磁盘块),以及太多的索引会导致文件系统大小限制所产生的问题,所以对哪些字段建立索引,什么情况下使用索引,需要审慎考虑。

由于索引只是用来加速数据查询,那么显然对只是用来输出的字段建立索引会浪费磁盘空间以及发生插入、删除操作时的处理时间,所以这种情况下应该尽量避免。此外鉴于二分搜索的特性,数据的基数或独立性是很重要的。在基数为2的字段上建立索引,将把数据分割一半,而基数为1000则将返回大约1000条记录。低基数的二分查找效率将降低为一个线性排序,而且查询优化器可能会在基数小于记录数某个比例时(如30%)的情况下将避免使用索引而直接查询原表,所以这种情况下的索引浪费了空间。
 
 

MySQL中不会使用到索引的情况总结

数据库zkbhj 发表了文章 • 0 个评论 • 652 次浏览 • 2017-09-27 15:58 • 来自相关话题

   众所周知,增加索引是提高查询速度的有效途径,但是很多时候,即使增加了索引,查询仍然不使用索引,这种情况严重影响性能,这里就简单总结几条MySQL不使用索引的情况
如果MySQL估计使用索引比全表扫描更慢,则不使用索引。例如,如果列key均匀分布在1和100之间,下面的查询使用索引就不是很好:select * from table_name where key>1 and key<90;如果使用MEMORY/HEAP表,并且where条件中不使用“=”进行索引列,那么不会用到索引,head表只有在“=”的条件下才会使用索引用or分隔开的条件,如果or前的条件中的列有索引,而后面的列没有索引,那么涉及到的索引都不会被用到,例如:select * from table_name where key1='a' or key2='b';如果在key1上有索引而在key2上没有索引,则该查询也不会走索引复合索引,如果索引列不是复合索引的第一部分,则不使用索引(即不符合最左前缀),例如,复合索引为(key1,key2),则查询select * from table_name where key2='b';将不会使用索引如果like是以‘%’开始的,则该列上的索引不会被使用。例如select * from table_name where key1 like '%a';该查询即使key1上存在索引,也不会被使用如果列为字符串,则where条件中必须将字符常量值加引号,否则即使该列上存在索引,也不会被使用。例如,select * from table_name where key1=1;如果key1列保存的是字符串,即使key1上有索引,也不会被使用。

    从上面可以看出,即使我们建立了索引,也不一定会被使用,那么我们如何知道我们索引的使用情况呢??在MySQL中,有Handler_read_key和Handler_read_rnd_key两个变量,如果Handler_read_key值很高而Handler_read_rnd_key的值很低,则表明索引经常不被使用,应该重新考虑建立索引。可以通过:show status like 'Handler_read%'来查看着连个参数的值。 查看全部
   众所周知,增加索引是提高查询速度的有效途径,但是很多时候,即使增加了索引,查询仍然不使用索引,这种情况严重影响性能,这里就简单总结几条MySQL不使用索引的情况
  1. 如果MySQL估计使用索引比全表扫描更慢,则不使用索引。例如,如果列key均匀分布在1和100之间,下面的查询使用索引就不是很好:select * from table_name where key>1 and key<90;
  2. 如果使用MEMORY/HEAP表,并且where条件中不使用“=”进行索引列,那么不会用到索引,head表只有在“=”的条件下才会使用索引
  3. 用or分隔开的条件,如果or前的条件中的列有索引,而后面的列没有索引,那么涉及到的索引都不会被用到,例如:select * from table_name where key1='a' or key2='b';如果在key1上有索引而在key2上没有索引,则该查询也不会走索引
  4. 复合索引,如果索引列不是复合索引的第一部分,则不使用索引(即不符合最左前缀),例如,复合索引为(key1,key2),则查询select * from table_name where key2='b';将不会使用索引
  5. 如果like是以‘%’开始的,则该列上的索引不会被使用。例如select * from table_name where key1 like '%a';该查询即使key1上存在索引,也不会被使用
  6. 如果列为字符串,则where条件中必须将字符常量值加引号,否则即使该列上存在索引,也不会被使用。例如,select * from table_name where key1=1;如果key1列保存的是字符串,即使key1上有索引,也不会被使用。


    从上面可以看出,即使我们建立了索引,也不一定会被使用,那么我们如何知道我们索引的使用情况呢??在MySQL中,有Handler_read_key和Handler_read_rnd_key两个变量,如果Handler_read_key值很高而Handler_read_rnd_key的值很低,则表明索引经常不被使用,应该重新考虑建立索引。可以通过:show status like 'Handler_read%'来查看着连个参数的值。

根据身份证号匹配性别并更新数据库的SQL

数据库zkbhj 发表了文章 • 0 个评论 • 1046 次浏览 • 2017-09-13 19:51 • 来自相关话题

update t_profile p set p.gender = 1 where p.uid in
(
select c.uid as a from t_cert c,t_profile z where c.uid=z.uid and c.cert_type =1
and SUBSTRING(c.cert_num,17,1) in (0,2,4,6,8) and LENGTH(c.cert_num)=18
and z.gender =2
);

update t_profile p set p.gender = 2 where p.uid in
(
select c.uid as a from t_cert c,t_profile z where c.uid=z.uid and c.cert_type =1
and SUBSTRING(c.cert_num,17,1) in (1,3,5,7,9) and LENGTH(c.cert_num)=18
and z.gender =1
); 查看全部
update t_profile p set p.gender = 1 where p.uid in 
(
select c.uid as a from t_cert c,t_profile z where c.uid=z.uid and c.cert_type =1
and SUBSTRING(c.cert_num,17,1) in (0,2,4,6,8) and LENGTH(c.cert_num)=18
and z.gender =2
);

update t_profile p set p.gender = 2 where p.uid in
(
select c.uid as a from t_cert c,t_profile z where c.uid=z.uid and c.cert_type =1
and SUBSTRING(c.cert_num,17,1) in (1,3,5,7,9) and LENGTH(c.cert_num)=18
and z.gender =1
);