基于Node.JS索引mp3 ID3 tag信息并存储入MySQL数据库

注意中文编码问题：客户端代码建立connection时将charset设置为binary，这个与服务器端的默认latin1是兼容的。

var id3 = require("node-id3"),path = require("path"),fs_walk = require('fs-walk'),MysqL = require('MysqL');

var pool = MysqL.createPool({
	host     : 'localhost',user     : 'root',password : '',database : 'musicDB',//一般sql数据库名称需要在外部创建，里面的table可以在代码里创建
	connectionLimit: 10,charset: 'binary'
});
/*
create table music_info(
    id INTEGER PRIMARY KEY AUTO_INCREMENT,absPath VARCHAR(512) NOT NULL,album VARCHAR(512),artist VARCHAR(512),title VARCHAR(512),tracknumber IntegeR,albumType CHAR
    );
*/

//debug:
pool.on('acquire',function (connection) {
  console.log('Connection %d acquired',connection.threadId);
});
pool.on('release',function (connection) {
  console.log('Connection %d released',connection.threadId);
});

fs_walk.walkSync('d:/Music',function(basedir,filename,stat) {
	console.log("walkSync: basedir="+basedir+" filename="+fileName);
	if(stat.isDirectory()){
		console.log("walkSync: skip dir");
	}else if(!filename.endsWith(".mp3")){
		console.log("walkSync: skip non-mp3 file");
	}
	else{
		var absPath = path.join(basedir,fileName)
		var tags = id3.read(absPath)
		//console.log("walkSync: tags="+JSON.Stringify(tags));
		var album = tags.album;
		var title = tags.title;
		var artist = tags.artist;
		var tracknumber = number(tags.tracknumber);
		
		//怎么获得sql INSERT返回的id主键值？
		//TODO: 尽管使用了pool，但假如每个INSERT请求都要创建一个connection，显然有性能问题
		//		而且这里应该使用批量插入BULK INSERT模式以提高性能
		var query = pool.query(/*{
			sql: "INSERT INTO `music_info`(`absPath`,`album`,`artist`,`title`,`tracknumber`) VALUES(?,?,?)",timeout: 20000,//20s
			values: [absPath,album,artist,title,tracknumber]
		  }*/
			"INSERT INTO `music_info` SET ?",{absPath: absPath,album: album,artist: artist,title: title,tracknumber: tracknumber},function(err,rows,fields){
				if(err){
					console.log("pool.query INSERT ERROR "+err);
				}else{
					console.log("pool.query INSERT succesS");
				}
			}
		);
		console.log("walkSync: query="+JSON.Stringify(query));
	}
});