随机数字id

MySQL不使用自增id,兼容异地分布。

创建临时id表

CREATE TABLE `t_tempid` (
`id` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

临时id生成脚本

#! /usr/bin/env node
const program = require('commander');
const fs = require('fs');
const path = require('path');
const { QUERY } = require('[email protected]/orm');
const TABLE = 'DATABASE.t_id';
const range = val => val.split('..').map(Number);
program
.version('1.0.0')
.usage('[options]')
.option('-r, --range <a>..<b>', 'Range 1..2 means 10000-19999', range)
.parse(process.argv);
if ((!program.range || program.range.length !== 2)) {
program.help();
process.exit();
}
const filename = path.join(__dirname, '../backup/', `id-${program.range.join('-')}.csv`);
const fd = fs.openSync(filename, 'w');
let block = '';
for (let i = program.range[0] * 10 ** 4; i < program.range[1] * 10 ** 4; i += 1) {
block += `${i}\n`;
if (i % 1000 === 0 || i === program.range[1] * 10 ** 4 - 1) {
fs.writeSync(fd, block);
block = '';
}
}
fs.closeSync(fd);
(async () => {
try {
await QUERY(`LOAD DATA LOCAL INFILE '${filename}' INTO TABLE ${TABLE}`);
} catch (e) {
console.trace(e);
}
process.exit();
})();

获取临时id并删除

const { isEmpty } = require('@xibang/node-common');
const { QUERY, format } = require('[email protected]/orm');
const TABLE = 'DATABASE.t_id';
exports.getTempId = async () => {
const sql = format('SELECT t1.id FROM ?? AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM ??)-(SELECT MIN(id) FROM ??))+(SELECT MIN(id) FROM ??)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 1', [TABLE, TABLE, TABLE, TABLE]);
const result = await QUERY(sql);
if (isEmpty(result)) {
return 0;
}
const id = result[0].id;
await QUERY(format('DELETE FROM ?? WHERE id = ? LIMIT 1', [TABLE, id]));
return id;
};