MySQL用SQL生成雪花ID

样例

• ID1是纯随机数
• ID2用到当前数据表的信息

SELECT
	CONCAT(
		'1',
		DATE_FORMAT( NOW(), '%H' ),
		LPAD( FLOOR( RAND() * 1000000 ), 6, 0 ),
		DATE_FORMAT( NOW(), '%i%s' ),
	LPAD(( SELECT @rowno := @rowno + 1 FROM ( SELECT @rowno := 0 ) t ) * 71, 4, 0 )) ID1,
	(
	SELECT
		CONCAT(
			LEFT ( UNIX_TIMESTAMP( tis.create_time ), 3 ),
			RIGHT ( UNIX_TIMESTAMP( NOW()), 7 ),
			LPAD((
					tis.DATA_LENGTH + tis.table_rows + tis.avg_row_length + tis.MAX_DATA_LENGTH + tis.INDEX_LENGTH + DATA_FREE + ( SELECT @rowno := @rowno + 1 FROM ( SELECT @rowno := 0 ) t ) * 71 
					) * 1777,
				9,
				0 
			)) 
	FROM
		information_schema.TABLES tis 
	WHERE
		tis.TABLE_SCHEMA = 'test' 
		AND TABLE_NAME = 't_project' 
	) id2,
	NAME
FROM
	test.t_project;

参考

MySQL 生成自编的19位雪花算法ID

【MySQL】查看库表信息