Mysql如何查询一张表里的数据不在另外一张表里?

回复

zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 4133 次浏览 • 2018-06-12 15:27 • 来自相关话题

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

zkbhj 发表了文章 • 0 个评论 • 1954 次浏览 • 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 个评论 • 2059 次浏览 • 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 个评论 • 2250 次浏览 • 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 固定数据库角色成员且不可转让。

如何用MySQL数据库的LEFT JOIN?

回复

zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 3841 次浏览 • 2017-12-20 19:29 • 来自相关话题

MySQL中的in查询,如何按期望的顺序排序?

回复

zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 4282 次浏览 • 2017-11-28 21:31 • 来自相关话题

常见的数据库有哪些?数据库分类是怎么样的?

回复

zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 3855 次浏览 • 2017-11-15 10:47 • 来自相关话题

什么是图形数据库?

回复

zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 2555 次浏览 • 2017-11-15 10:33 • 来自相关话题

什么是NoSQL数据库?

回复

zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 4028 次浏览 • 2017-11-15 10:31 • 来自相关话题

如何拼SQL到数据到MySQL表里更新部分字段?

回复

zkbhj 回复了问题 • 1 人关注 • 1 个回复 • 4031 次浏览 • 2017-10-20 12:21 • 来自相关话题