Introduction to MySQL BIGINT Data Type

BIGINT is a commonly used data type in MySQL for storing large integer values.

Syntax

The BIGINT data type can be used to store integers between -9223372036854775808 and 9223372036854775807. Its syntax is as follows:

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

Where M represents the display width, which can be in the range of 1 to 20. UNSIGNED is used to indicate that the column does not contain negative values, and ZEROFILL is used to pad with zeros when the display width is not met.

Use Cases

The BIGINT data type is commonly used for storing integers that exceed the upper limit of the INT data type. For example, in computer science, BIGINT is often used for storing very large numbers such as hash values, file sizes, and so on. In the financial domain, BIGINT is often used for storing currency amounts and stock trading volumes.

Examples

Here are two examples of using the BIGINT data type:

This example uses BIGINT to store file sizes.

CREATE TABLE file_sizes (
  file_name VARCHAR(255) NOT NULL,
  size BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (file_name)
);

INSERT INTO file_sizes (file_name, size) VALUES
  ('file1.txt', 102401238401),
  ('file2.txt', 3847283123),
  ('file3.txt', 98213233844);

In the above example, we create a table called file_sizes with two columns: file_name and size. The size column uses the BIGINT UNSIGNED data type because file sizes cannot be negative. We insert three example records into the table, each containing a file name and its corresponding size.

Next, we use BIGINT to store currency amounts:

CREATE TABLE orders (
  order_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id INT UNSIGNED NOT NULL,
  amount BIGINT NOT NULL,
  PRIMARY KEY (order_id)
);

INSERT INTO orders (customer_id, amount) VALUES
  (1, 5000000000),
  (2, 2500000000),
  (3, 10000000000);

In the above example, we create a table called orders with three columns: order_id, customer_id, and amount. The amount column uses the BIGINT data type because the amounts can be very large. We insert three example records into the table, each containing a customer ID and an order amount.

Conclusion

In this article, we introduced the BIGINT data type in MySQL. BIGINT is commonly used for storing integers that exceed the upper limit of the INT data type, such as hash values, file sizes, currency amounts, and stock trading volumes. When using the BIGINT data type, it’s important to be aware of its storage range and display width.