BIGINT в MySQL. Введение
BIGINT — 64‑битный целочисленный тип для больших идентификаторов, счётчиков и сумм в «центах». Ниже — синтаксис, типовой шаблон и 100 практических сценариев.
Синтаксис
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(255) NOT NULL
);ALTER TABLE orders
MODIFY COLUMN customer_id BIGINT NOT NULL;SELECT id, total_cents
FROM invoices
WHERE total_cents BETWEEN 100 AND 500000;SELECT CAST(JSON_EXTRACT(meta,'$.uid') AS UNSIGNED) AS uid
FROM events;Типовая конструкция
CREATE TABLE payments (
payment_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
amount_cents BIGINT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX (user_id)
);100 примеров
Маркетплейс: первичный ключ AUTO_INCREMENT
CREATE TABLE orders_1 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);Финансы: первичный ключ AUTO_INCREMENT
CREATE TABLE invoices_2 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);IoT: первичный ключ AUTO_INCREMENT
CREATE TABLE metrics_iot_3 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);Соцсеть: первичный ключ AUTO_INCREMENT
CREATE TABLE likes_4 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);Логирование: первичный ключ AUTO_INCREMENT
CREATE TABLE logs_5 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);CRM: первичный ключ AUTO_INCREMENT
CREATE TABLE customers_6 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);Аналитика: первичный ключ AUTO_INCREMENT
CREATE TABLE user_stats_7 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);Телеком: первичный ключ AUTO_INCREMENT
CREATE TABLE phone_book_8 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);Реклама: первичный ключ AUTO_INCREMENT
CREATE TABLE ad_clicks_9 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);Игры: первичный ключ AUTO_INCREMENT
CREATE TABLE game_events_10 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);Маркетплейс: первичный ключ AUTO_INCREMENT
CREATE TABLE orders_11 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);Финансы: первичный ключ AUTO_INCREMENT
CREATE TABLE invoices_12 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);IoT: первичный ключ AUTO_INCREMENT
CREATE TABLE metrics_iot_13 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);Соцсеть: первичный ключ AUTO_INCREMENT
CREATE TABLE likes_14 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);Логирование: первичный ключ AUTO_INCREMENT
CREATE TABLE logs_15 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);CRM: первичный ключ AUTO_INCREMENT
CREATE TABLE customers_16 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);Аналитика: первичный ключ AUTO_INCREMENT
CREATE TABLE user_stats_17 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);Телеком: первичный ключ AUTO_INCREMENT
CREATE TABLE phone_book_18 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);Реклама: первичный ключ AUTO_INCREMENT
CREATE TABLE ad_clicks_19 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);Игры: первичный ключ AUTO_INCREMENT
CREATE TABLE game_events_20 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);Маркетплейс: внешний ключ на BIGINT
CREATE TABLE orders_child_21 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
parent_id BIGINT UNSIGNED NOT NULL,
CONSTRAINT fk_orders_21 FOREIGN KEY (parent_id) REFERENCES orders_1(id)
);Финансы: внешний ключ на BIGINT
CREATE TABLE invoices_child_22 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
parent_id BIGINT UNSIGNED NOT NULL,
CONSTRAINT fk_invoices_22 FOREIGN KEY (parent_id) REFERENCES invoices_2(id)
);IoT: внешний ключ на BIGINT
CREATE TABLE metrics_iot_child_23 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
parent_id BIGINT UNSIGNED NOT NULL,
CONSTRAINT fk_metrics_iot_23 FOREIGN KEY (parent_id) REFERENCES metrics_iot_3(id)
);Соцсеть: внешний ключ на BIGINT
CREATE TABLE likes_child_24 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
parent_id BIGINT UNSIGNED NOT NULL,
CONSTRAINT fk_likes_24 FOREIGN KEY (parent_id) REFERENCES likes_4(id)
);Логирование: внешний ключ на BIGINT
CREATE TABLE logs_child_25 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
parent_id BIGINT UNSIGNED NOT NULL,
CONSTRAINT fk_logs_25 FOREIGN KEY (parent_id) REFERENCES logs_5(id)
);Ещё примеры
CRM: внешний ключ на BIGINT
CREATE TABLE customers_child_26 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
parent_id BIGINT UNSIGNED NOT NULL,
CONSTRAINT fk_customers_26 FOREIGN KEY (parent_id) REFERENCES customers_6(id)
);Аналитика: внешний ключ на BIGINT
CREATE TABLE user_stats_child_27 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
parent_id BIGINT UNSIGNED NOT NULL,
CONSTRAINT fk_user_stats_27 FOREIGN KEY (parent_id) REFERENCES user_stats_7(id)
);Телеком: внешний ключ на BIGINT
CREATE TABLE phone_book_child_28 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
parent_id BIGINT UNSIGNED NOT NULL,
CONSTRAINT fk_phone_book_28 FOREIGN KEY (parent_id) REFERENCES phone_book_8(id)
);Реклама: внешний ключ на BIGINT
CREATE TABLE ad_clicks_child_29 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
parent_id BIGINT UNSIGNED NOT NULL,
CONSTRAINT fk_ad_clicks_29 FOREIGN KEY (parent_id) REFERENCES ad_clicks_9(id)
);Игры: внешний ключ на BIGINT
CREATE TABLE game_events_child_30 (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
parent_id BIGINT UNSIGNED NOT NULL,
CONSTRAINT fk_game_events_30 FOREIGN KEY (parent_id) REFERENCES game_events_10(id)
);Инкремент счётчика просмотров
UPDATE pageviews SET views = views + 1 WHERE id=123;
JSON→BIGINT при импорте событий
1)
SELECT CAST(JSON_EXTRACT(meta,'$.user_id') AS UNSIGNED) AS user_id
FROM events;2)
SELECT CAST(JSON_EXTRACT(meta,'$.user_id') AS UNSIGNED) AS user_id
FROM events;3)
SELECT CAST(JSON_EXTRACT(meta,'$.user_id') AS UNSIGNED) AS user_id
FROM events;4)
SELECT CAST(JSON_EXTRACT(meta,'$.user_id') AS UNSIGNED) AS user_id
FROM events;5)
SELECT CAST(JSON_EXTRACT(meta,'$.user_id') AS UNSIGNED) AS user_id
FROM events;6)
SELECT CAST(JSON_EXTRACT(meta,'$.user_id') AS UNSIGNED) AS user_id
FROM events;7)
SELECT CAST(JSON_EXTRACT(meta,'$.user_id') AS UNSIGNED) AS user_id
FROM events;
SELECT CAST(JSON_EXTRACT(meta,'$.user_id') AS UNSIGNED) AS user_id
FROM events;9)
SELECT CAST(JSON_EXTRACT(meta,'$.user_id') AS UNSIGNED) AS user_id
FROM events;10)
SELECT CAST(JSON_EXTRACT(meta,'$.user_id') AS UNSIGNED) AS user_id
FROM events;Ещё примеры
Топ клиентов по сумме в центах
SELECT customer_id, RANK() OVER (ORDER BY SUM(total_cents) DESC) AS r FROM orders GROUP BY customer_id;
Скользящая сумма по платежам
SELECT user_id, created_at, amount_cents, SUM(amount_cents) OVER (PARTITION BY user_id ORDER BY created_at ROWS 9 PRECEDING) AS sum10 FROM payments;
Скользящая сумма по платежам
SELECT user_id, created_at, amount_cents,
SUM(amount_cents) OVER (PARTITION BY user_id ORDER BY created_at ROWS 9 PRECEDING) AS sum10
FROM payments;Пагинация по PK без OFFSET
SELECT id, data FROM big_rows WHERE id > 9000000000 ORDER BY id LIMIT 100;
Ещё примеры
Диагностика шарда по модулю
1)
SELECT id
FROM big_table
WHERE id % 1000 = 7
ORDER BY id
LIMIT 100;2)
SELECT id
FROM big_table
WHERE id % 1000 = 7
ORDER BY id
LIMIT 100;3)
SELECT id
FROM big_table
WHERE id % 1000 = 7
ORDER BY id
LIMIT 100;4)
SELECT id
FROM big_table
WHERE id % 1000 = 7
ORDER BY id
LIMIT 100;5)
SELECT id
FROM big_table
WHERE id % 1000 = 7
ORDER BY id
LIMIT 100;Контроль целостности FK BIGINT
1)
SELECT o.id
FROM orders o
LEFT JOIN customers c ON c.id=o.customer_id
WHERE c.id IS NULL;2)
SELECT o.id
FROM orders o
LEFT JOIN customers c ON c.id=o.customer_id
WHERE c.id IS NULL;3)
SELECT o.id
FROM orders o
LEFT JOIN customers c ON c.id=o.customer_id
WHERE c.id IS NULL;4)
SELECT o.id
FROM orders o
LEFT JOIN customers c ON c.id=o.customer_id
WHERE c.id IS NULL;5)
SELECT o.id
FROM orders o
LEFT JOIN customers c ON c.id=o.customer_id
WHERE c.id IS NULL;Жёсткий предохранитель для DELETE
1)
DELETE FROM sensitive
WHERE id BETWEEN 0 AND 9999999999999999
AND 1=0;2)
DELETE FROM sensitive
WHERE id BETWEEN 0 AND 9999999999999999
AND 1=0;3)
DELETE FROM sensitive
WHERE id BETWEEN 0 AND 9999999999999999
AND 1=0;4)
DELETE FROM sensitive
WHERE id BETWEEN 0 AND 9999999999999999
AND 1=0;5)
DELETE FROM sensitive
WHERE id BETWEEN 0 AND 9999999999999999
AND 1=0;Ещё примеры
UUID→BIGINT‑хеш (первые 8 байт)
SELECT CAST(CONV(SUBSTRING(REPLACE(uuid, '-', ''), 1, 16), 16, 10) AS UNSIGNED) AS id64
FROM uuid_map;Форматирование суммы в рублях из центов
SELECT SUM(amount_cents) AS cents,
CONCAT(FLOOR(SUM(amount_cents)/100), '.', LPAD(SUM(amount_cents)%100,2,'0')) AS amount
FROM invoices;Сборка 64‑битового идентификатора из high/low
SELECT (CAST(high AS UNSIGNED) << 32) + CAST(low AS UNSIGNED) AS combined
FROM parts;Разбор 64‑битового идентификатора на high/low
SELECT (id64 >> 32) AS high,
(id64 & 4294967295) AS low
FROM ids64;Контроль переполнения сумм в отчёте
SELECT CASE WHEN SUM(amount_cents) > 9000000000000000000 THEN 'overflow' ELSE 'ok' END AS state
FROM invoices;Ещё примеры
Срез по рыночной капитализации v96
SELECT asset_id, cap
FROM market_caps
WHERE cap BETWEEN 1000000000 AND 100000000000;Экспорт ID как строки для CSV v97
SELECT CAST(id AS CHAR) AS id_txt
FROM accounts;Индекс по foreign key BIGINT v98
CREATE INDEX ix_orders_customer
ON orders (customer_id);Хранение суммы денег в центах v99
CREATE TABLE invoices_core (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
amount_cents BIGINT NOT NULL
-- v99
);;RANGE COLUMNS по org_id v100
CREATE TABLE balances_core (
id BIGINT,
org_id BIGINT,
amount BIGINT
-- v100
);
PARTITION BY RANGE COLUMNS(org_id -- v100
); (
PARTITION p1 VALUES LESS THAN (1000 -- v100
);,
PARTITION p2 VALUES LESS THAN (2000 -- v100
);,
PARTITION pmax VALUES LESS THAN (MAXVALUE -- v100
);
-- v100
);;Типичные ошибки и советы по BIGINT в MySQL
- Знак: выбирайте UNSIGNED для идентификаторов и неотрицательных счётчиков.
- Переполнения: контролируйте верхние границы в UPDATE и агрегатах.
- Индексы: добавляйте индексы на JOIN и фильтры по диапазонам.
- JSON: приводите типы через CAST при чтении из JSON.
- Деньги: храните суммы в центах или используйте DECIMAL по требованиям финансов.
BIGINT в MySQL. Заключение
BIGINT масштабирует идентификаторы и счётчики, помогает аналитике и хранению денег. Правильно подбирайте знак и индексы.