工作中常用的mysql操作

一、在一个数据库中定义的存储过程或者是函数在另一数据库的存储过程中引用

1、我首先在数据库player_db中构建了一个函数：p_exp_to_level

BEGIN

    DECLARE v_level INT DEFAULT -1;

    SELECT `level` INTO v_level FROM `tbl_level_exp` WHERE `level_exp` <= p_exp ORDER BY `level` DESC LIMIT 1;

    RETURN v_level;

END

2、现在我想在另一个数据库gamedata中使用上面定义的函数，调用方式如下：

BEGIN
    #Routine body goes here...
  SELECT player_db.p_exp_to_level(exp) AS herolevel
  FROM player_db.player_info;
END

在调用的时候只需要指定数据库名就好了（dbName.func()）。

二、关于在存储过程中使用INTO

今天在写存储过程的时候本来定义了两个变量，想通过一个select，查找两列，然后把查找的内容赋值给定义的这两个变量，我首先是这么做的：

BEGIN

   DECLARE mintime VARCHAR(50);   
   DECLARE maxtime VARCHAR(50);   

   IF ISNULL(user_id) || LENGTH(TRIM(user_id)) < 1
   THEN
      SELECT -1;
   ELSE 
      SELECT SUM(GameIsWin) AS todayWin,SUM(KillCount) AS todayKill,Sum(AssistCount) AS todayAssist,SUM(MaxContinuousKill) AS todayContKill,MaxContinuousKill
      FROM tbl_playerdata
      WHERE PlayerID = user_id AND TO_DAYS(CreateDate) = TO_DAYS(NOW())
      GROUP BY MaxContinuousKill;
   
      SELECT MIN(GameStartTime) INTO mintime,MAX(GameStartTime) INTO maxtime FROM tbl_playerdata;
      
      SELECT SUM(GameIsWin) AS totalWin,SUM(KillCount) AS totalKill,SUM(AssistCount) AS totalAssist,SUM(MaxContinuousKill) AS totalContKill,MaxContinuousKill 
      FROM tbl_playerdata 
      WHERE GameStartTime BETWEEN mintime AND maxtime
      GROUP BY MaxContinuousKill;
      
   END IF;
END

在保存的时候报了如下错：（我使用的是Navicat for Mysql）

然后我把这一行改成了，如下形式，却正常了。。以后要注意了。。。（一个查询只能使用一个INTO）

BEGIN

   DECLARE mintime VARCHAR(50);   
   DECLARE maxtime VARCHAR(50);   

   IF ISNULL(user_id) || LENGTH(TRIM(user_id)) < 1
   THEN
      SELECT -1;
   ELSE 
      SELECT SUM(GameIsWin) AS todayWin,SUM(KillCount) AS todayKill,Sum(AssistCount) AS todayAssist,SUM(MaxContinuousKill) AS todayContKill,MaxContinuousKill
      FROM tbl_playerdata
      WHERE PlayerID = user_id AND TO_DAYS(CreateDate) = TO_DAYS(NOW())
      GROUP BY MaxContinuousKill;
   
      SELECT MIN(GameStartTime),MAX(GameStartTime) INTO mintime,maxtime FROM tbl_playerdata;
      
      SELECT SUM(GameIsWin) AS totalWin,SUM(KillCount) AS totalKill,SUM(AssistCount) AS totalAssist,SUM(MaxContinuousKill) AS totalContKill,MaxContinuousKill 
      FROM tbl_playerdata 
      WHERE GameStartTime BETWEEN mintime AND maxtime
      GROUP BY MaxContinuousKill;
      
   END IF;
END

三、今天再往数据库中手动插入数据的时候，里面明明插入了数据，却查询不出来。。

原因是数据库的数据后面有不可见字符（例如空格、制表、回车等），用下面的语句可以验证：

SELECT CONCAT('[',c,']'),LENGTH(c) FROM abcd

这个语句对字段C的前后添加[]显示，如果有空格可能会显示出来，同时显示c的长度，如果比你看见的多，肯定有问题。你的数据库需要使用下面的语句来修复：

UPDATE abcd SET c=TRIM(c)

四、mysql清空某个数据表

清空数据库内指定表内容的SQL语句:

1、delete tablename

2、TRUNCATE TABLE '表名'

delete、truncate区别在于：

1、truncate 比 delete删除速度快，truncate不可回滚，而delete可以回滚。

这是由于delete 语句每次删除一行，会在undo表空间中有记录存放。因此可以对delete操作进行roll back。而truncate 是被隐式提交，不能对 truncate 使用ROLLBACK命令。 

2、truncate 将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过 truncate 操作后的表比Delete操作后的表要快得多。 

3、当表被truncate清空后，表和表的索引将重新设置成初始大小，而delete则不能。

常用delete，因为可以回滚。其实即使是使用delete也要谨慎，不仅仅要考虑数据可否找回来，还可能会因为事务繁忙导致服务器夯住，或其他一些问题的出现！

五、 navicat for mysql只导出数据表结构

选中需要导出表结构的数据库，右键，在显示的菜单中选择“数据传输”这一项 ，在弹出窗口中“数据传输”单击选择“高级”一项，在“高级”中把“记录选项”中的勾去掉，在做一些设置，最后导出表数据就不会导出记录了。

即选中数据库--》右键—》数据传输—》高级—》取消勾选记录选项。

六、数据去重

DELETE FROM `table`
WHERE
`去重字段名` IN (
    SELECT x FROM
    (
        SELECT `去重字段名` AS x 
        FROM `table` 
        GROUP BY `去重字段名` 
        HAVING COUNT(`去重字段名`) > 1
    ) tmp0
)
AND 
`递增主键名` NOT IN (
    SELECT y FROM
    (
        SELECT min(`递增主键名`) AS y 
        FROM `table` 
        GROUP BY `去重字段名` 
        HAVING COUNT(`去重字段名`) > 1
    ) tmp1
)

 七、记一次查询插入操作

 insert INTO tbl_player_statistic SELECT PlayerID, SUM(KillCount + AssistCount + GameIsWin) AS Kill_A, SUM(MaxMultiKill) AS MultiKill, ac_user.tbl_user.createtime, NOW()
  FROM tbl_playerdata LEFT JOIN ac_user.tbl_user ON tbl_playerdata.PlayerID = ac_user.tbl_user.id
  WHERE PlayerID IN (SELECT DISTINCT PlayerID
  from tbl_playerdata WHERE LENGTH(PlayerID) > 9) AND (GameResult = 2 OR GameResult = 3)
  GROUP BY PlayerID;

 八、MySql多表关联Update笔记

对单表执行更新没有什么好说的，无非就是update table_name set col1 = xx,col2 = yy where col = zz，主要就是where条件的设置。有时候更新某个表可能会涉及到多张数据表，例如：

update table_1 set score = score + 5 where uid in (select uid from table_2 where sid = 10);

其实update也可以用到left join、inner join来进行关联，可能执行效率更高，把上面的sql替换成join的方式如下：

update table_1 t1 inner join table_2 t2 on t1.uid = t2.uid set score = score + 5 where t2.sid = 10;