MySQL - INT

MySQL - The INT Data Type

The INT (or INTEGER) data type is a fundamental numeric type in MySQL used to store whole numbers. MySQL offers several integer types to efficiently store numbers of different sizes, allowing you to optimize storage space.

Choosing the right integer type is a trade-off between the range of values you need and the storage space you want to use. Using the smallest type that can safely hold your data is a best practice.

Type	Storage (Bytes)	Minimum Value (Signed)	Maximum Value (Signed)	Maximum Value (Unsigned)
TINYINT	1	-128	127	255
SMALLINT	2	-32,768	32,767	65,535
MEDIUMINT	3	-8,388,608	8,388,607	16,777,215
INT	4	-2,147,483,648	2,147,483,647	4,294,967,295
BIGINT	8	-9,223,372,036,854,775,808	9,223,372,036,854,775,807	18,446,744,073,709,551,615

The AUTO_INCREMENT Attribute

The AUTO_INCREMENT attribute allows MySQL to automatically generate a unique integer for a new row. It is commonly used for primary key columns.

• Primary Key: An AUTO_INCREMENT column must be a key (usually the PRIMARY KEY).
• Unique Values: When you insert a NULL or 0 into an AUTO_INCREMENT column, MySQL automatically assigns the next sequence number.
• Sequence Reseeding: If you explicitly insert a higher value, the sequence is reseeded to that value + 1 for the next insertion.
• Getting the ID: After an INSERT operation, you can retrieve the last generated ID using the LAST_INSERT_ID() function.

Example

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL
);

-- Insert some rows
INSERT INTO products (product_name) VALUES ('Laptop'), ('Mouse');

-- Check the generated IDs
SELECT * FROM products;
-- Result will show product_id 1 for Laptop, 2 for Mouse

-- Find out the last ID that was inserted in this session
SELECT LAST_INSERT_ID(); -- Result: 2

The UNSIGNED Attribute

By default, integer types are SIGNED, meaning they can store both positive and negative values. If a column will only ever store non-negative numbers (like a product ID or a count), you can declare it as UNSIGNED. This effectively doubles the maximum positive value the column can hold.

Example

CREATE TABLE inventory (
    item_id INT UNSIGNED NOT NULL,
    quantity INT UNSIGNED NOT NULL
);

-- This insert works perfectly
INSERT INTO inventory (item_id, quantity) VALUES (101, 50);

-- This insert will fail with an 'Out of range' error
INSERT INTO inventory (item_id, quantity) VALUES (102, -5);

Deprecated Attributes: Display Width and ZEROFILL

⚠️ **Important Notice:** The `ZEROFILL` attribute and the concept of a `display width` for integer types (e.g., `INT(11)`) are **deprecated** as of MySQL 8.0.17 and will be removed in a future version. You should avoid them in new development.

In the past, INT(11) did not restrict the size of the integer. It was a hint for applications about how many characters to use for display, often in conjunction with ZEROFILL. ZEROFILL would pad the number with leading zeros up to the display width.

Modern Alternative: Application-Side Formatting

Formatting is a presentation-layer concern and should be handled in your application code or, if necessary, with SQL formatting functions like LPAD().

-- Storing a number
CREATE TABLE legacy_orders (order_id INT);
INSERT INTO legacy_orders VALUES (123);

-- Formatting the output for display using SQL
-- This is the modern way to achieve zero-padding.
SELECT LPAD(order_id, 8, '0') AS formatted_order_id FROM legacy_orders;

-- Output:
-- +--------------------+
-- | formatted_order_id |
-- +--------------------+
-- | 00000123           |
-- +--------------------+

Using INT in a Client Program

When creating tables from a client program, you simply specify the integer type. Any formatting should be handled when you display the data.

Example (Python with mysql-connector-python)

import mysql.connector

# Database connection config
config = {
    'user': 'root',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'testdb'
}

try:
    with mysql.connector.connect(**config) as connection:
        with connection.cursor() as cursor:
            # Drop table if it exists for a clean run
            cursor.execute("DROP TABLE IF EXISTS users")

            # Create table with an INT AUTO_INCREMENT primary key
            create_table_query = '''
            CREATE TABLE users (
                user_id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) NOT NULL,
                login_attempts INT UNSIGNED DEFAULT 0
            )
            '''
            cursor.execute(create_table_query)
            print("Table 'users' created successfully.")

            # Insert a record and get the new ID
            insert_query = "INSERT INTO users (username) VALUES (%s)"
            cursor.execute(insert_query, ('john_doe',))
            new_id = cursor.lastrowid
            connection.commit()
            print(f"New user inserted with ID: {new_id}")

except mysql.connector.Error as err:
    print(f"Error: {err}")