MySQL踩过的那些坑

最新推荐文章于 2021-03-01 11:10:34 发布

原创最新推荐文章于 2021-03-01 11:10:34 发布 · 309 阅读

0 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

1 篇文章

订阅专栏

本文围绕MySQL展开，介绍了update语句报错‘You can’t specify target table’的解决办法，即增加中间表；指出使用‘!=’会过滤null值，可将null改为空字符串或查询时添加null条件；还讲解了多表关联插入、更新及删除操作，最后提及了MySQL授权。

MySQL踩过的那些坑

MySQL update语句报错：You can't specify target table 'xxx' for update in FROM clause
MySQL使用 != 会过滤null的问题
MySQL多表关联插入、更新及删除
MySQL授权

MySQL update语句报错：You can’t specify target table ‘xxx’ for update in FROM clause

UPDATE member
SET yj_email_flag=3
WHERE
	id IN (
		SELECT
			id
		FROM
			member
		WHERE
			moneytime < '2019-01-24'
	);

这里意思大概是：在同一语句中，不能先select出同一表中的某些值，再update这个表。
解决方案：查询的时候增加一层中间表，就可以避免该错误。

UPDATE member
SET yj_email_flag=3
WHERE
	id IN (
	SELECT t.id from
		(SELECT
			id 
		FROM
			member
		WHERE
			moneytime < '2019-01-24') t
	);

MySQL使用 != 会过滤null的问题

简单来讲，数据库里有三条数据，一条为空字符串，一条为null，一条有数据（eg:数据为8）
那么查询使用 !=8 只能查询到一条数据，一条空字符串的数据，由此可见，**!=**把null值也过滤掉了；
解决方案：

将数据库中的null改为空字符串（此处update需注意，需要先查询自身，再更改数据）

update std set a1088='' where a100 in(
	select t.a100 from(select a100 from std where a1088 is null) t
);

查询的时候，把为null的条件也加上去

select * from std where a1088 != '08' or a1088 is null;

MySQL多表关联插入、更新及删除

我感觉这些用于直接操作数据库来更新数据
1.用一张表的数据来更新另一张表（关联更新）

update student s, city c
   set s.city_name = c.name
 where s.city_code = c.code;
 或者
update student s set city_name = (select name from city where code = s.city_code);

2.关联删除
从MySQL数据表t1中把那些id值在数据表t2里有匹配的记录全删除掉

DELETE t1 FROM t1,t2 WHERE t1.id=t2.id 
或 DELETE FROM t1 USING t1,t2 WHERE t1.id=t2.id

3.用一张表的数据插入另一张表中

INSERT INTO db1_name(field1,field2) SELECT field1,field2 FROM db2_name

有三张表a、b、c，现在需要从表b和表c中分别查几个字段的值插入到表a中对应的字段

INSERT INTO a(field1,field2) SELECT * FROM(SELECT f1,f2 FROM b JOIN c) AS tb

其中f1是表b的字段，f2是表c的字段，通过join查询就将分别来自表b和表c的字段进行了组合，然后再通过select嵌套查询插入到表a中，这样就满足了我们这个场景了，如果需要不止2个表，那么可以多个join的形式来组合字段。需要注意的是嵌套查询部分最后一定要有设置表别名

MySQL授权

grant all privileges on 库名.表名 to '用户名'@'IP地址' identified by '密码' with grant option;
grant all privileges on \*.\* to 'root' @'%' identified by 'root';   --给root用户授权全部，密码是root
grant all privileges on 数据库.* to 'root' @'%' identified by 'root';--给root用户授权指定数据库，密码是root
flush privileges;   --刷新权限
注意：如果授权时，出现以下错误：Access denied for user 'root'@'%' to database '**'  有可能是root用户没用授权权限。
解决方法：select Host,User,Grant_priv,Super_priv from mysql.user;//先查询root是否有授权权限，没有则进行修改
update mysql.user set Grant_priv='Y',Super_priv='Y' where user = 'root' and host = '%';
flush privileges;   重启mysql