Basic knowledge of

Basic principle: a 64 – byte integer consisting of four parts (bigint).

  • Byte computation reads from left to right, and the subscript count starts at 0.
  • The 0th bit is the sign bit: positive numbers are 0 and negative numbers are 1. However, some languages do not have unsigned integers (Java \PostgreSQL), so this is fixed to 0 for portability.
  • Bits 1 to 41 are time stamps: the unit is milliseconds. The timestamp is only 41 bits, so the value ranges from 0 to 2199023255551 milliseconds. Up to 69 years of data can be stored. Because the value range is small, you cannot use UNIX timestamps directly. Instead, use the timestamp difference (current time – the time you specify to start). You can specify any start time. For example, if the system starts development in 2020, the start time can be 2020-1-1.
select (2199023255551: :bigint) / (24: :bigint * 60 * 60 * 1000) / 365
Copy the code
  • Bits 42-51 are machine ids: you can combine the 10 bits yourself. For example, if all the hosts are in the CLASS C IP address segment (0-255), use the last bit of the IP address as the host ID. At this point, only 8 bytes are needed, and the extra 2 bytes can be allocated to the timestamp. At this time, the timestamp occupies 43 bytes, and the value range is 0-8796093022207. The storage range can be expanded to 278 years.
select (((1: :bigint)<<43) - 1) / (24: :bigint * 60 * 60 * 1000) / 365
Copy the code
  • Sequence number 52-63: The value ranges from 0 to 4095. It is the number of ids that can be generated by a machine at the same timestamp. That is, a machine can generate a maximum of 4095 ids at the same millisecond.
select (((1: :bigint)<<12) - 1)
Copy the code

Bitwise operation technique

To set any 1 bit of the bit to 0, set that bit to 0, the other bits to 1, and then perform the & operation. For example, set bit 7 to 0

select (32767::bit(16)) &  x'FF7F'
-- 32767 binary representation is 01111111 11111111
-- FF7F binary representation is 11111111 01111111
-- result 01111111 01111111
Copy the code

To set up any one bit bit to 1, this bit is set to 1, the other is set to 0, then execute | operation. For example, set bit 7 to 1

select (0::bit(16)) &  x'0080'
-- 0 binary: 00000000 00000000
-- 0080 binary is 00000000 10000000
-- Result 00000000 10000000
Copy the code

Use SQL to implement snowflake algorithm snowflake

select 
	id>>22,
	((id<<41)&9223372036854775807)>>53,
	((id<<51)&9223372036854775807)>>51
from (select (((2199023255551: :bigint)<<22) | (1023<<12) | 4095 ) as id) as tmp
Copy the code
  • 2199023255551: indicates the maximum value of the timestamp
  • 1023: indicates the maximum number of machine ids
  • 4095: indicates the maximum number of serial numbers
  • Note: The data cannot be restored if the input value exceeds the maximum value, so you must first scope to see if the range is in a valid range before generating.
  • 9223372036854775807 is to ensure that the sign bit is set to 0, because the algorithm dictates that the sign bit must be 0.
--9223372036854775807 binary
0111111111111111111111111111111111111111111111111111111111111111
Copy the code
  • The sign bit must be preserved when the machine ID and sequence number shift left, so the machine ID moves 41 bits to the left and the sequence number moves 51 bits to the left. After the left shift is complete, the sign bit is not set to 0, so the sign bit must be guaranteed to be 0 after the left shift.

variant

In the use of distributed programs, the same machine in the same millisecond will only produce a very small number of serial numbers, so you can expand the working machine ID to 12, 14, 16 bits, serial numbers using 10, 8, 6 bits. The specific choice should be based on your requirements.

  • Working machine ID 12-bit value range :1-4095
  • Working machine ID 14-bit value range :1-16383
  • Working machine ID 16-bit value range :1 to 65535
  • Serial number 10-digit value range :1-1023
  • Serial number 8-bit value range :1-255
  • Serial number 6-digit value range :1-63

Working machine id12 digit, serial number 10 digit

select 
	id>>22,
	((id<<41)&9223372036854775807)>>51,
	((id<<53)&9223372036854775807)>>53
from (select (((2199023255551: :bigint)<<22) | (4095<<10) | 1023 ) as id) as tmp
Copy the code

Working machine id14 bits, serial number 8 bits

select 
	id>>22,
	((id<<41)&9223372036854775807)>>49,
	((id<<55)&9223372036854775807)>>55
from (select (((2199023255551: :bigint)<<22) | (16383<<8) | 2 ) as id) as tmp
Copy the code

Working machine id16 bits, serial number 6 bits

select 
	id>>22,
	((id<<41)&9223372036854775807)>>47,
	((id<<57)&9223372036854775807)>>57
from (select (((2199023255551: :bigint)<<22) | (65535<<6) | 63 ) as id) as tmp
Copy the code