Integers are a basic but essential component in database design, especially when working with MySQL. This article will guide you through everything you need to know about the INT data type in MySQL, from creating tables to manipulating data, ensuring you maximize the performance and accuracy of your databases.
What is the INT data type in MySQL?
The INT data type is used to store integer numeric values, that is, numbers without decimal parts. In MySQL, an INT takes up 4 bytes of storage and can represent both positive and negative values. The range of values is from -2,147,483,648 to 2,147,483,647. For projects that require storing numbers outside this range, MySQL offers other variants such as TINYINT, SMALLINT, MEDIUMINT and BIGINT.
Creating a table with INT column
To get started with integer data, you must first know how to create a table that includes this type of data. Here I show you how you can do it:
CREATE TABLE employees ( id INT AUTO_INCREMENT, name VARCHAR(100), INT age, PRIMARY KEY(id) );In this example, age is a column of type INT. Note how it is used AUTO_INCREMENT for the column id, this is common in INT columns intended to be primary keys.
Inserting and modifying data in INT columns
Insert data
Once your table is set up, adding data is simple. Here is an example of how to insert data into the table employees:
INSERT INTO employees (name, age) VALUES ('Ana López', 32);Update data
If you need to change the information, you will use the UPDATE command. For example, if Ana López has a birthday and you want to update her age:
UPDATE employees SET age = 33 WHERE name = 'Ana López';Consultations with INT
Integers are frequently used to filter and sort data in SQL queries. For example, if you wanted to find all employees over the age of 30, you could use the following command:
SELECT * FROM employees WHERE age > 30;Sorting results
You can also sort the results based on an INT column. If you want to sort employees by age in descending order, you would use:
SELECT * FROM employees ORDER BY age DESC;This will give you a list of employees starting with the oldest.
Indexes on INT columns
Implementing indexes on columns that use the INT type can significantly improve the speed of lookup operations, especially on large tables. Here I show you how to add an index to the column age:
CREATE INDEX idx_age ON employees (age);This index will allow MySQL to quickly find employees based on their age, which is useful for queries that filter by this criteria.
Performance considerations
Although INTs are generally efficient, choosing the right type of INT (such as TINYINT or BIGINT) based on the range of data you plan to store can save storage space and improve performance. For example, if you only need to store values between 0 and 255, using TINYINT instead of INT is more efficient.
Conclusion
The INT data type is a powerful tool in MySQL that helps you handle numeric data. Mastering how to create tables with INT, insert and manipulate data, as well as optimize with indexes, will allow you to design more efficient and effective databases.
If you want more details or have any questions, do not hesitate to visit NelkoDev or get in touch via this link. Keep exploring and improving your database management skills!
English 
Spanish