#bigint

MySQL 的 TIME 範圍

這篇算是考古文，找出 MySQL 裡 TIME 資料型態奇怪範圍的由來：「TIME for a WTF MySQL moment」。

在官方的文件裡面可以看到 TIME 的範圍是個奇怪的數字，如果把各版本的文件都拉出來看，會發現都沒改過：「11.2.3 The TIME Type (8.0)」、「11.2.3 The TIME Type (5.7)」、「11.2.3 The TIME Type (5.6)」，「11.3.2 The TIME Type (5.5，靠 Internet Archive 的存檔頁面)」、「11.3.2 The TIME Type (5.1，靠 Internet Archive 的存檔頁面)」、「11.3.2 The TIME Type (5.0，靠 Internet Archive 的存檔頁面)」，裡面一直都是：

TIME values may range…

Firefox 68: BigInts, Contrast Checks, and the QuantumBar

https://hacks.mozilla.org/files/2019/07/scroll-snap.mp4

Firefox 68 is available today, featuring support for big integers, whole-page contrast checks, and a completely new implementation of a core Firefox feature: the URL bar.

These are just the highlights. For complete information, see:

BigInts for JavaScript

Firefox 68 now supports JavaScript’s new BigInt numeric type.

Since its introduction,…

Firefox 引入 BigInt，Safari 也在實作…

Firefox 實作了 BigInt (進度可以在「Implementation of BigInt values for SpiderMonkey」這邊看到)：「bigint shipping in firefox!」，現在可以在 68 beta 版裡使用：

I am delighted to share with folks the results of a project I have been helping out on for the last few months: implementation of “BigInt” in Firefox, which is finally shipping in Firefox 68 (beta).

另外文中也提到了其他瀏覽器的情況 (再 Can I Use 也可以看到「BigInt」目前的支援情況)，用 V8 engine…

Facebook 在 MySQL 裡存時間的型態

在 MySQL at Facebook 的這邊說明提到了，Facebook 內部是使用 INT UNSIGNED 儲存時間： Which gets us to the point that it is no different than storing INT (hello 2038?) or UNSIGNED INT (a bit later) or BIGINT (till the end of time) and possibly passing binary values in efficient protocols eventually. If you got that far of this post, your likes in Facebook graph are stored with ‘INT UNSIGNED’ time field. 順道一提，INT 是 2038…

The inspiration for this post comes from the mysql forum.

The problem is common enough: you want your SQL tables to have unique, secure primary keys. You also don't want to kill your database performance by using string UUIDs as keys. You also don't want serialized auto numbering for keys that may present security risks later on.

One very solid solution to this problem is an IDs table. That is, create a table that holds the list of all important IDs to be used by all other tables in your schema. Such a table might look like this:

create table ids ( id bigint unsigned not null primary key, taken tinyint default 0 );

Any time we run out of IDs, we can generate new ones with this stored procedure:

CREATE PROCEDURE create_ids (create_id_num INT) BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' BEGIN END; SET @i = 1; loop1: LOOP INSERT INTO ids (id) VALUES (FLOOR(1 + RAND() * POW(2,63))); SET @i = @i + 1; IF @i > create_id_num THEN LEAVE loop1; END IF; END LOOP loop1; END;

Now when one of your tables needs a new id, simply grab a random open one from the IDs table:

SELECT id FROM ids WHERE taken = 0 LIMIT 100; UPDATE ids SET taken=1 WHERE id=? AND taken=0;

In scripting language land, grab a set of ids and attempt to make the UPDATE with one of them. If the update fails, then some other process grabbed that ID. That's no biggie, just try another.

What if we run out of IDs? Create a trigger that ensures that there are more IDs created when the last one is taken:

CREATE TRIGGER make_additional_ids AFTER UPDATE ON ids FOR EACH ROW BEGIN IF (SELECT COUNT(*) FROM ids WHERE taken = 0) = 0 THEN CALL create_ids(1000); END IF; END;