MySQL - INT: A Beginner's Guide

Hello, aspiring database enthusiasts! Today, we're going to dive into the world of MySQL and explore one of its most fundamental data types: INT. Don't worry if you're new to programming – I'll guide you through this journey step by step, just like I've done for countless students over my years of teaching. So, let's roll up our sleeves and get started!

MySQL - INT

The MySQL INT Data Type

The INT data type in MySQL is like the strong, dependable workhorse of the numeric data types family. It's used to store whole numbers (integers) without any decimal points. Think of it as a box that can hold numbers from -2,147,483,648 to 2,147,483,647. That's a lot of numbers!

Let's create a simple table to see INT in action:

CREATE TABLE my_first_table (
    id INT,
    age INT,
    score INT
);

In this example, we've created a table called my_first_table with three columns: id, age, and score. All of these columns use the INT data type.

Now, let's add some data:

INSERT INTO my_first_table (id, age, score) VALUES (1, 25, 95);
INSERT INTO my_first_table (id, age, score) VALUES (2, 30, 88);
INSERT INTO my_first_table (id, age, score) VALUES (3, 22, 100);

Here, we're adding three rows of data to our table. Each INSERT statement adds a new person with their id, age, and score.

Auto Increment with MySQL INT

One of the cool features of INT is that it can automatically increment. This is super useful when you want to generate unique identifiers for each row in your table. Let me show you how it works:

CREATE TABLE auto_increment_example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO auto_increment_example (name) VALUES ('Alice');
INSERT INTO auto_increment_example (name) VALUES ('Bob');
INSERT INTO auto_increment_example (name) VALUES ('Charlie');

In this example, the id column will automatically assign 1, 2, and 3 to Alice, Bob, and Charlie respectively. It's like having a helpful assistant numbering your entries for you!

MySQL INT UNSIGNED

Sometimes, you only need positive numbers. That's where UNSIGNED comes in handy. It effectively doubles the maximum value an INT can hold, from 0 to 4,294,967,295.

CREATE TABLE positive_numbers (
    id INT UNSIGNED,
    quantity INT UNSIGNED
);

INSERT INTO positive_numbers (id, quantity) VALUES (1, 100);
INSERT INTO positive_numbers (id, quantity) VALUES (2, 4294967295);

This table will only accept positive numbers, which is perfect for things like quantities or ages.

MySQL INT with Display Width Attribute

The display width attribute doesn't affect the range of values an INT can store, but it can make your data look prettier when displayed. Here's how it works:

CREATE TABLE display_width_example (
    id INT(4),
    code INT(6) ZEROFILL
);

INSERT INTO display_width_example (id, code) VALUES (1, 123);
INSERT INTO display_width_example (id, code) VALUES (1000, 456789);

When you retrieve this data, code will be displayed as 000123 and 456789 respectively. It's like adding leading zeros to make all your numbers the same width.

MySQL INT with ZEROFILL Attribute

ZEROFILL is like the display width's best friend. It automatically pads your numbers with zeros to reach the specified width. Let's see it in action:

CREATE TABLE zerofill_example (
    id INT(5) ZEROFILL
);

INSERT INTO zerofill_example (id) VALUES (1);
INSERT INTO zerofill_example (id) VALUES (100);
INSERT INTO zerofill_example (id) VALUES (10000);

When you retrieve this data, you'll see 00001, 00100, and 10000. It's great for creating consistent-looking codes or IDs.

INT Datatype Using a Client Program

Now, let's put all this knowledge into practice using a client program. I'll use the MySQL command-line client for this example, but the principles apply to any MySQL client.

-- Connect to MySQL
mysql -u your_username -p

-- Create a database
CREATE DATABASE int_examples;

-- Use the database
USE int_examples;

-- Create a table using various INT types
CREATE TABLE int_showcase (
    id INT AUTO_INCREMENT PRIMARY KEY,
    regular_int INT,
    unsigned_int INT UNSIGNED,
    display_width_int INT(4),
    zerofill_int INT(6) ZEROFILL
);

-- Insert some data
INSERT INTO int_showcase (regular_int, unsigned_int, display_width_int, zerofill_int)
VALUES (-1000, 1000, 123, 123);

INSERT INTO int_showcase (regular_int, unsigned_int, display_width_int, zerofill_int)
VALUES (2000000, 3000000, 45678, 45678);

-- Retrieve the data
SELECT * FROM int_showcase;

When you run this code, you'll see how each INT type behaves differently. The regular_int can hold negative values, the unsigned_int only positive, the display_width_int looks normal, and the zerofill_int adds those leading zeros.

Here's a table summarizing the INT variations we've covered:

INT Type	Range	Example Usage
INT	-2,147,483,648 to 2,147,483,647	General purpose integer storage
INT UNSIGNED	0 to 4,294,967,295	Positive integers only
INT AUTO_INCREMENT	Same as INT, but automatically increases	Primary keys, unique identifiers
INT(N)	Same as INT, but affects display width	Formatting output
INT(N) ZEROFILL	Same as INT, pads with zeros	Creating consistent-width codes

And there you have it, folks! We've journeyed through the land of MySQL INTs, from the basics to some nifty tricks. Remember, like learning any new skill, mastering MySQL takes practice. So don't be afraid to experiment with these concepts. Who knows? You might just become the INT-expert in your coding circle! Happy querying!

Credits: Image by storyset