MySql – 第 2 页

MySql 在update中实现子查询

MySql更新语句中，经常会碰到需要使用子查询限定更新范围的情况，但是mysql更新语句是不支持子查询的

#例如这样当使用mysql条件更新时--最先让人想到的写法  
UPDATE user SET isagent=1 WHERE uid IN (SELECT uid FROM user_agent) 

#此语句是错误的，会报错 You can't specify target table 'xxx' for update in FROM

这是因为：

mysql的update的一些特点

1、update 时，更新的表不能在set和where中用于子查询；

2、update 时，可以对多个表进行更新（sqlserver不行）；

     如：update ta a,tb b set a.Bid=b.id ,b.Aid=a.id;

3、update 后面可以做任意的查询，这个作用等同于from；

正确的方式是，例：

#简单的更新语句
UPDATE `user` u SET u.`status`=1 WHERE u.`isagent`='1'

#复杂的更新语句
例子1
update member m,(SELECT * FROM FansRole) r set m.agentlevel=(case r.FanskindID when 1 then 0 else r.FanskindID+2 end),m.`level`=r.FanskindID,m.isagent=1,m.`status`=1,m.agenttime=UNIX_TIMESTAMP(r.RegTime) where m.id=r.UserID
例子2
UPDATE order_mall a,(SELECT order_mall.id FROM `order_mall`,`order_goods` WHERE order_mall.`id`=order_goods.`order_id` AND order_goods.order_status=8 AND order_goods.order_goods_type=3) b 
SET a.`status`=4
WHERE a.id=b.id

MySQL中随机生成固定长度字符串的方法

在MySQL中有时需要随机生成指定位数数字或字符串，随机生产数字可直接使用rand()函数，但是要随机生成字符串就比较麻烦。

要随机生成字符串代码如下：
在MySQL中定义一个随机串的方法，然后在MySQL语句中调用此方法。

CREATE DEFINER=`root`@`localhost` FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET latin1
BEGIN
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));
        SET i = i +1;
    END WHILE;
    RETURN return_str;
END;

定义完后，在MySQL语句中调用即可

update member set salt=rand_string(6);

Mysql字符串拼接方法

Mysql中字符串连接

SELECT '12'+'32','1abc'+'22','abc'+'3'
#结果为：44,2,3
#并非我们想要的1232,1abc22,abc3
#注：在Mysql中，使用“+”进行字符连接时，mysql会尝试将字段值转换为数字类型（如果转换失败，就当做数字0处理）。如’1abc’+’22′，mysql将“1abc”转成数字1在进行运算；将“abc”当做0处理。

所以在Mysql中要使用函数CONCAT(str1,str2,…)来进行字符串拼接，CONCAT函数支持一个或者多个参数，参数类型可以为字符串类型也可以是非字符串类型，对于非字符串类型的参数MYSQL将尝试将其转化为字符串类型，CONCAT函数会将所有参数按照参数的顺序拼接成一个字符串做为返回值。

SELECT CONCAT('车型：',type,',颜色:',color) FROM car;
#结果：“车型：紧凑，颜色：骚粉”

项目从MySql5.5迁移到MySql5.7 timestamp默认值不能为空问题

今天客户项目从开发环境搬迁到生产环境时，因为mysql版本不一致，导致部分表格无法迁移
具体原因为Mysql默认不允许timestamp设置0000-00-00 00:00:00的默认值
经过一番搜索，得到几种解决方案
最后方便起见
直接把timestamp格式字段默认值改成了2000-01-01 00:00:00

常见的方法为修改mysql配置文件，把NO_ZERO_IN_DATE,NO_ZERO_DATE这两个配置去掉

在my.cnf[mysqld]下添加

sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

MySql having用法

Mysql中，where是聚合前筛选条件，用于行列数据进入，不可结合函数使用；而having则是用于聚合后数据的筛选，可以接合函数使用,一般having 跟在group by后面使用。
以下是实际使用：
找出person表中重复的邮箱

#方法1 使用临时表 语句比较复杂
select s.email from (
    select email,count(email) as num from person group by email 
) as s 
where s.num>1

#方法2 使用having
select email from person group by email having count(email)>1

#注意having属于聚合后筛选，应在where后，且放置于order by后面

mysql 关联查询索引不起作用原因记录

今天联表查询的时候发现，两个表的’openid’都已经加了索引，联表的使用也是用的索引字段，理论上应该能命中索引，但是explain分析索引失效了，导致查询平均时间从0.01暴涨到平均十几秒

SELECT l.*,m.nickname,m.id AS member_id,m.mobile,m.avatar,m.realname,m.weixin,m1.nickname AS m1_nickname,m1.id AS m1_id,m1.mobile AS m1_mobile,m1.avatar AS m1_avatar,m1.realname AS m1_realname,m2.nickname AS m2_nickname,m2.id AS m2_id,m2.mobile AS m2_mobile,m2.avatar AS m2_avatar,m2.realname AS m2_realname,ml.logno,ml.createtime AS rechargetime,ml.rechargetype FROM `ims_vcshop_reward` l LEFT JOIN `ims_vcshop_member` m ON l.openid=m.openid LEFT JOIN `ims_vcshop_member` m1 ON l.m1=m1.id LEFT JOIN `ims_vcshop_member` m2 ON l.m2=m2.id LEFT JOIN `ims_vcshop_member_log` ml on ml.id=l.logid WHERE l.uniacid=2  ORDER BY l.id DESC limit 0,50

修改后命中索引

最后通过百度大法发现
1、两表关联使用的条件字段中字段的长度是否是一致的（本人测试不影响，规范期间数据库相同字段还是要保持长度一致）

2、两表关联使用的条件字段中字段的编码是否是一致的

如果以上两种情况不满足，也会导致索引失效；
本人在此就是’openid’字段，分别使用了utf8和utf8mb4两种编码，导致索引失效；

Mysql 5.7 5.6 5.5新增保留字

Mysql5.6新增保留字

GET	IO_AFTER_GTIDS	IO_BEFORE_GTIDS
MASTER_BIND	ONE_SHOT	PARTITION
SQL_AFTER_GTIDS	SQL_BEFORE_GTIDS

Mysql5.7新增保留字

ACCOUNT	ALWAYS	CHANNEL
COMPRESSION	ENCRYPTION	FILE_BLOCK_SIZE
FILTER	FOLLOWS	GENERATED (R)
GROUP_REPLICATION	INSTANCE	JSON
MASTER_TLS_VERSION	NEVER	OPTIMIZER_COSTS (R)
PARSE_GCOL_EXPR	PRECEDES	REPLICATE_DO_DB
REPLICATE_DO_TABLE	REPLICATE_IGNORE_DB	REPLICATE_IGNORE_TABLE
REPLICATE_REWRITE_DB	REPLICATE_WILD_DO_TABLE	REPLICATE_WILD_IGNORE_TABLE
ROTATE	STACKED	STORED (R)
VALIDATION	VIRTUAL (R)	WITHOUT
XID

MySql 使用CASE WHEN语法条件性处理数据

假设数据库有字段 id,money1,money2,m1,m2,查询时需要判断mid=m1则累计money1，mid=m2则累计money2，此时可以使用CASE WHEN语法条件性处理数据

SELECT SUM(
    CASE WHEN m1=:id THEN money1 WHEN m2=:id THEN money2 ELSE 0 END
) AS money FROM TABLE
# Tips：匹配m1=:id时，返回money1；匹配m2=:id时，返回money2，否则返回0

无限分类-树形结构两种方式

//准备数组，代替从数据库中检索出的数据(共有三个必须字段id,name,pid) 
header("content-type:text/html;charset=utf-8"); 
 $categories = array( array('id'=>1,'name'=>'电脑','pid'=>0), array('id'=>2,'name'=>'手机','pid'=>0), array('id'=>3,'name'=>'笔记本','pid'=>1), array('id'=>4,'name'=>'台式机','pid'=>1), array('id'=>5,'name'=>'智能机','pid'=>2), array('id'=>6,'name'=>'功能机','pid'=>2), array('id'=>7,'name'=>'超级本','pid'=>3), array('id'=>8,'name'=>'游戏本','pid'=>3), ); /*方法1 利用引用*/ /*第一步 先组建以id为key的数组*/$ tree = array();
foreach( $categories as$ v){
   $tree[$ v['id']] =  $v;$ tree[ $v['id']]['children'] = array(); } /*第二部 利用引用，将children添加进去,这样只遍历一次即可*/ foreach($ tree as  $key=>$ v){
  if( $v['pid'] != 0){$ tree[ $v['pid']]['children'] = &$ tree[ $key];//注意：此处必须传引用否则结果不对 } } /*第三部 删除无用的根节点*/ foreach($ tree as  $key=>$ v){
  if( $v['pid'] !=0 ){ unset($ tree[ $key]); } } /*方法2 利用递归*/ function get_attr($ arr, $pid){$ tree = array();
  foreach( $arr as$ key=> $v){ if($ v['pid'] ==  $pid){$ v['children'] = get_attr( $arr,$ v['id']);
      if(empty( $v['children'])){ unset($ v['children']);//如果为空，则删除，可选
      }
       $tree[] =$ v;
    }
  }
  return  $tree; }$ tree2 = get_attr($categories, 0);