Ⅰ sql踩坑记录-Update多个字段
最近遇到了把我弊磨坑惨的一个update语句：
update apps set owner = �' and owner_name = 'xiaoming' where owner_code = �' and owner_name = 'lihua'
在码昌MySQL里面update一条记录，语法都正确的，但记录并没有被更新...
刚遇到这个问题的时候，我拿到这条语句直接在测试库里面执行了好几次，发现确实有问题。
测试SQL： update apps set owner = �' and owner_name = '李四' where owner_code = �' and owner_name = '张三'
执行之前的记录是这样的：
执行之后的记录是这样的：
可以看到，结果并不像这位开发同学说的“好像没有效果”，实际上是有效果的：
看起来，语法是完全没有问题，翻了翻MySQL官方文档的update语法迟卜扒：
看到assignment_list的格式是以逗号分隔的col_name=value列表，一下子豁然开朗，开发同学想要的多字段更新语句应该这样写：
倒回去再重试验一把：
果然，这下得到了想要的结果！
小结 ： 在一条UPDATE语句中，如果要更新多个字段，字段间不能使用“AND”，而应该用逗号分隔。
后记 ： 后面等有空的时候，又回过头来看了一下，为什么使用“AND”分隔的时候，会出现owner_code=0的奇怪结果？多次尝试之后发现：
等价于：
而 (�' and owner_name='李四') 是一个逻辑表达式，而这里不难知道owner_name并不是‘李四’。
因此，这个逻辑表达式的结果为 false ， false在MySQL中等价于0！
Ⅱ 怎么跳出MySQL的10个大坑
有两种方法，一种方法使用mysql的check table和repair table 的掘举迟sql语句，另一种方法是使用MySQL提供的多判李个myisamchk, isamchk数据检测恢复工具。前者使用起来比较简便。推荐使用。
1. check table 和 repair table
登陆mysql 终端：
mysql -uxxxxx -p dbname
check table tabTest;
如果出现的结果说Status是OK，则不用修复，如果有Error，可以用：
repair table tabTest;
进行修复，修复之后可以在用check table命令来进行检查。在答局新版本的phpMyAdmin里面也可以使用check/repair的功能。
2. myisamchk, isamchk
其中myisamchk适用于MYISAM类型的数据表，而isamchk适用于ISAM类型的数据表。这两条命令的主要参数相同，一般新的系统都使用MYISAM作为缺省的数据表类型，这里以myisamchk为例子进行说明。当发现某个数据表出现问题时可以使用：
myisamchk tablename.MYI
进行检测，如果需要修复的话，可以使用：
myisamchk -of tablename.MYI
关于myisamchk的详细参数说明，可以参见它的使用帮助。需要注意的时在进行修改时必须确保MySQL服务器没有访问这个数据表，保险的情况下是最好在进行检测时把MySQL服务器Shutdown掉。
Ⅲ 怎么跳出MySQL的10个大坑
一种方碧姿指法使用mysql的check table和repair table 的sql语句
另一种方法是使用MySQL提悔配供的多个myisamchk, isamchk数据检测恢复工具。前册数者使用起来比较简便。
Ⅳ 经验分享：8种常见SQL错误用法
1、LIMIT 语句
分页查询是最常用的场景之一，但也通常也是最容易出问题的地方。比如对于下面简单的语句，一般 DBA 想到的办法是在 type, name, create_time 字段上加组合索引。这样条件排序都能有效的利用到索引，性能迅速提升。
好吧，可能90%以上的 DBA 解决该问题就到此为止。但当 LIMIT 子句变成 “LIMIT 1000000,10” 时，程序员仍然会抱怨：我只取10条记录为什么还是慢？
要知道数据库也并不知道第1000000条记录从什么地方开始，即使有索引也需要从头计算一次。出现这种性能问题，多数情形下是程序员偷懒了。
在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值当成参数作为查询条件的。SQL 重新设计如下：
在新设计下查询时间基本固定，不会随着数据量的增长而发生变化。
2、隐式转换
SQL语句中查询变量和字段定义类型不匹配是另一个常见的错误。比如下面的语句：
其中字段 bpn 的定义为 varchar(20)，MySQL 的策略是将字符串转换为数字之后再比较。函数作用于表字段，索引失效。
上述情况可能是应用程序框架自动填入的参数，而不是程序员的原意。现在应用框架很多很繁杂，使用方便的同时也小心它可能给自己挖坑。
3、关联更新、删除
虽然 MySQL5.6 引入了物化特性，但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成 JOIN。
比如下面 UPDATE 语句，MySQL 实际执行的是循环/嵌套子查询（DEPENDENT SUBQUERY)，其执行时间可想而知。
执行计划：
重写为 JOIN 之后，子查询的选择模式从 DEPENDENT SUBQUERY 变成 DERIVED，执行速度大大加快，从7秒降低到2毫秒。
执行计划简化为：
4、混合排序
MySQL 不能利用索引进行混合排序。但在某些场景，还是有机会使用特殊方法提升性能的。
执行计划显示为全表扫描：
由于 is_reply 只有0和1两种状态，我们按照下面的方法重写后，执行时间从1.58秒降低到2毫秒。
5、EXISTS语句
MySQL 对待 EXISTS 子句时，仍然采用嵌套子查询的执行方式。如下面的 SQL 语句：
执行计划为：
去掉 exists 更改为 join，能够避免嵌套子查询，将执行时间从1.93秒降低为1毫秒。
新的执行计划：
6、条件下推
外部查询条件不能够下推到复杂的视图或子查询的情况有：
如下面的语句，从执行计划可以看出其条件作用于聚合子查询之后：
确定从语义上查询条件可以直接下推后，重写如下：
执行计划变为：
关于 MySQL 外部条件不能下推的详细解释说明请参考文章：
7、提前缩小范围
先上初始 SQL 语句：
该SQL语句原意是：先做一系列的左连接，然后排序取前15条记录。从执行计划也可以看出，最后一步估算排序记录数为90万，时间消耗为12秒。
由于最后 WHERE 条件以及排序均针对最左主表，因此可以先对 my_order 排序提前缩小数据量再做左连接。SQL 重写后如下，执行时间缩小为1毫秒左右。
再检查执行计划：子查询物化后（select_type=DERIVED)参与 JOIN。虽然估算行扫描仍然为90万，但是利用了索引以及 LIMIT 子句后，实际执行时间变得很小。
8、中间结果集下推
再来看下面这个已经初步优化过的例子(左连接中的主表优先作用查询条件)：
那么该语句还存在其它问题吗？不难看出子查询 c 是全表聚合查询，在表数量特别大的情况下会导致整个语句的性能下降。
其实对于子查询 c，左连接最后结果集只关心能和主表 resourceid 能匹配的数据。因此我们可以重写语句如下，执行时间从原来的2秒下降到2毫秒。
但是子查询 a 在我们的SQL语句中出现了多次。这种写法不仅存在额外的开销，还使得整个语句显的繁杂。使用 WITH 语句再次重写：
总结
数据库编译器产生执行计划，决定着SQL的实际执行方式。但是编译器只是尽力服务，所有数据库的编译器都不是尽善尽美的。
上述提到的多数场景，在其它数据库中也存在性能问题。了解数据库编译器的特性，才能避规其短处，写出高性能的SQL语句。
程序员在设计数据模型以及编写SQL语句时，要把算法的思想或意识带进来。
编写复杂SQL语句要养成使用 WITH 语句的习惯。简洁且思路清晰的SQL语句也能减小数据库的负担 。
Ⅳ 记一次MySQL Group by 的坑
表结构如下：
业务需要将表中重复数据删除，所以需要按照 组合唯一索引键筛选出重复的数据进行删除。SQL如下：
表中有符合索引 KEY column1_column2_index ( column1 , column2 )
sql语句 Group by 也是按照最左匹配原则顺序写的 group by 的字段，但是每次执行SQL耗时都是好几十秒
explain 该 sql 发现，并没有走表中存在的复合索引，而是直接走的 File sorted（文件排序）；group by 语句其实是顷虚有要先排序再分组念悉的；
问题的关键定位到没有没有命中表中的复合索引，那为何 group by 字段前两个就是复合索引，只是最后两个不是，为何没有走索引呢？不是索引只要满足最左匹配原则就可以命中吗？
分析后发现，索引可以用在两个地方，1 被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。2 快速地执行ORDER BY和GROUP BY语句的排序和分组操作。
本处就是可以使用索引做排序使用，而避免文件排序；此处要命中索引，走索引排序，必须要表中有一个复合索引包含 group by 的所有字段且顺序一致；
网上有部分博客说 group by 自带的排序和 order by 排序，走不走索引的规则是一样的，这里本人测试了一下，添加 group by 后面所有顺序字段的复合仔乎乎索引对 group by 的查询时间有直接的影响，从 30多秒 优化到 3秒；
但是对如下SQL 的执行时间也有影响，但是远远没有对group by 的影响大，如下sql，添加 order by 的全索引后 只能从30多秒优化到 10 多秒
select column2 from am_cm_relationship order by column1,column2,column3,column4 ;
Ⅵ mac MySQL的那些坑之mysql: command not found
第一次安装好MySQL后, 打开终端准备输入 mysql -uroot -p 发现报错:
按照网址上的命令在终端输入了一下,
然后再输入 mysql -uroot -p 发现又报错了.
系统偏好设置< MySQL < Start MySQL Server
显示如图就说明已经开启了SQL服务:
好吧, 接下来又激铅接着输入 mysql -uroot -p 发现终于允许敲密码了!
登录初喊铅岁始密码, 然后立马换密码啊! 怎么更换密码, 请看我的另一篇文章~
]( http://www.jianshu.com/p/0f23874705be )
然后就可以使用mysql, 但是我突然想起一个事
果然不出我所料. 也不能每次都输入上面的两个语句吧.
学习就是这样, 从一个坑跳到另一个坑~
怎么样能够一劳永逸呢? 可以去修改环境变量.
STEP 1
输入如下命令修改home目录下的 .bash_profile 文件，如果之前没有进行过环境变量的配置，那么该文件将是空白的无内容，执行如下命令
STEP 2
并将下面这句话👇 原封不动 复制到 .bash_profile 文件中
记得 保存 (command+S), 然后关闭.
这郑睁一步完成之后彻底关闭命令行
重新打开就可以使用的MySQL命令了.
然而 , 由于我是新电脑, 根本就没有bash_profile文件, 所以报了这个错:
自然就是创建一个 .bash_profile 文件了.
打开终端开始输入:
STEP 1
*注: * 进入HOME文件夹中
STEP 2
*注: * 创建一个 .bash_profile 文件
STEP 3
然后就与上面相同, 打开.bash_profile 文件
STEP 4
将下面这句话👇 原封不动 复制到 .bash_profile 文件中, 记得 保存 (command+S), 然后关闭.
希望mac MySQL的那些坑这一系列文章能够帮助大家尽快入门~~
Ⅶ Mysql NULL之踩坑记
在开发过程中踩过的坑，在此做下记录，谨防下次入坑。
例：表table
执行sql 如下
得到结帆皮果如下
第一列state为null的也态余差被过滤掉
解决办法1
解决办法2
踩坑原因
大多数编程语言都是基于二值逻辑的毁卜，即逻辑值只有true、false 两个。而sql语言采用的是三值逻辑，除了true、false 之外还有一个“unknown”。
即所有值与NULL比较都返回不确定
Ⅷ SQL Server datatime类型精度的坑
datetime类型的精度为 ：千分之三秒（3.33毫秒、0.00333秒）。所有的datetime类型的值在显示、处理时 有所调整。
即会圆整到几个特殊的毫秒个位值：0、3、7：如：(9、0、1） 会引起进0调整；（5、6、7、8）引起7调整；（2、3、4）引起3调整。
做一个小测试
你会发现 @dt 的值变为了 2020-07-01 00:00:00.000
那么查询的坑就来了。
你明明想只查 2020.6.30 出生的数据。 where birthday>='2020-06-30 00:00:00.000' and birthday<='2020-06-30 23:59:59.999'
结果7.1号出生的也查询出来了。
要更精确的秒数位数 可使用 datetime2类型。datatime2 秒支持到7位小数
查询出来dt的结果就是 2020-06-30 23:59:59.9990000