MySQLにnode.jsでBMP外の文字を保存してみるテスト

unicode_test.js

/*
 * CREATE TABLE `test` (
 *   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 *   `text` text,
 *   `binary` blob,
 *   PRIMARY KEY (`id`)
 * ) ENGINE=InnoDB DEFAULT CHARSET=utf8
 * 
 */

var mysql = require('mysql');

var client = mysql.createClient({
	user: 'username',
	password: 'password',
	host: 'localhost',
	database: 'test'
});

client.on('error', function (err) {
	console.error(err);
});

var data = JSON.parse('"\\uD83D\\uDCA9\\uD83D\\uDCA9\\uD83D\\uDCA9"');

// `binary`の方に正しく保存される気がする

client.query(
	'INSERT INTO `test` SET `text`=?, `binary`=0x' + bin2hex(data),
	[ data ],
	function (err, info) {
		if (!err) {
			console.log(info);
		} else {
			console.error(err);
		}
		client.end();
	}
);

function bin2hex (string, callback) {
	try {
		var encoded = encodeURIComponent(string),
			binary = eval('"' + encoded.replace(/%/g, '\\x') + '";'),
			buffer = new Buffer(binary, 'binary'),
			result = '';
		
		for (var i = 0, j = buffer.length; i < j; i ++)
			result += ('0' + buffer.readUInt8(i).toString(16)).slice(-2);
	} catch (err) {
		err = new Error('Invalid arguments');
		
		if (callback) {
			process.nextTick(function() {
				callback(err, null);
			});
		} else {
			throw err;
		}
	}
	
	if (callback) {
		process.nextTick(function() {
			callback(null, result);
		});
	} else {
		return result;
	}
}