破万卷总结五

MySQL字符串转日期

使用date_format

1	> select * from tairlist_day where date_format(rq,'%Y-%m-%d')>'2010-08-01' and date_format(rq,'%Y-%m-%d')<'2010-08-03'

使用str_to_date()

1	select * from h_hotelcontext where now() between STR_TO_DATE(Start_time,'%Y-%m-%d %H:%i:%s') and STR_TO_DATE(End_time,'%Y-%m-%d %H:%i:%s');

关于MySQL的count

select * from table group by id having count(t=’100’ or Null )>1 and count(t=’103’ or null)>2

关于MySQL的限制条数查询

sqlserver是select top 1…
MySQL是select from … limit n
还有区间查法select from … limit 0,30查询前30条记录。

关于nodejs MySQL事务处理

var Dao=require('./Dao');
var async = require('async');
var pool=new Dao();
module.exports = function transDao(sqlparamEntities,callback){
	pool.getConnection(function(err,connection){
		if(err){
			return callback(err,null);
		}
		connection.beginTransaction(function(err){
			if(err){
				return callback(err,null);
			}
			var funcAry = [];
			sqlparamEntities.forEach(function(sql_param){
				var temp = function(cb){
					var sql = sql_param.sql;
					var param = sql_param.param;
					connection.query(sql,param,function(err,rows,fields){
						if(err){
							connection.rollback(function(){
								console.log(err);
								throw err;
							});
						}else{
							return cb(null,'ok');
						}
					});
				};
				funcAry.push(temp);
			});
			async.series(funcAry,function(err,result){
				console.log(err);
				if(err){
					connection.rollback(function(err){
						console.log(err);
						return callback(err,null);
					});
				}else{
					connection.commit(function(err,info){
						console.log(err);
						if(err){
							connection.rollback(function(err){
								console.log(err);
								connection.release();
							});
						}else{
							connection.release();
							return callback(null,info);
						}
					})
				}
			})
		});
	});
};