工作中常用的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只导出数据表结构

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

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

六、mysql批处理更新

主要是使用IN操作：

function updateLoginStat(callback) {    var userList = ["109526258500519971", "109526258500519972", "109526258500519973", "109526258500519974", "109526258500519975", "109526258500519976", "109526258500519977", "109526258500519978", "109526258500519979", "109526258500519980","109526258500519981"];    var newDate = moment(new Date()).format("YYYY-MM-DD HH:mm:ss");   //  格式化时间    var sql = "UPDATE stat_login SET timestamp = " + "now()" + " WHERE user_id IN (" + userList + ") AND opt = 'logout'";    console.log("sql:", sql);    mysqlPool1.query(sql, function (err, results) {        if (err) {            console.log("EEEEEEEEEEEEEEEE");            console.log(err.toString());            callback(err, null);        } else {            console.log("SSSSSSSSSSSSSSSSSSS");            console.log(results);            callback(null, null);        }    });}

上面的操作是批处理更新时间。。。

 七、记一次查询插入操作

以前是使用分页查询，然后一个个做操作。。。根据同事指导。。一个查询搞定：

 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;

 八、node批量插入数据

function insertPlayerData(query, cb) {    var _sql = "insert into orders(order_num, order_date, cust_id) values ?";    var values = [[20012, '2016-02-12 12:01:15', 10001], [2013, '2016-02-12 12:01:15', 10002], [20014, '2016-02-12 12:01:15', 10003]];   mysqlPool2.query(_sql, [values], function(err, result){        if(err || !result.affectedRows < 0){            console.log("错误信息：", err);            cb(err, null);        }else{            cb(null);        }    })}