How to Cast as Integer in MySQL

In this tutorial, we aim at exploring how to cast a data type as int in MySQL.

The CAST method in MySQL helps us cast a particular value to a specified data type. This is generally used to change data types from one type to another.

It can be critically important in development as well as production environments to ensure correct and efficient datatypes are assigned to columns.

Let us understand how this method works.

However, before we begin, we create a dummy dataset to work with.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

CAST in MySQL

The basic syntax of the CAST technique can be illustrated as follows.

SELECT CAST(column_name) AS data_type FROM name_of_table;

As we can see, in the aforementioned query, the column_name refers to the name of the column for which we aim to change or assign the data type which is showcased as data_type. The changes would be reflected in the table named name_of_table in the above query.

Now let us try to cast the stu_id column to integer values from floating point values. This can be done as follows.

SELECT CAST(stu_id as UNSIGNED) as casted_values FROM student_details;

The aforementioned code casts the stu_id column to UNSIGNED integer values from the student_details table. The output of the aforementioned code is as follows:

casted_values
1
2
3
4
5
6
7

Therefore, with the help of the CAST technique, we can efficiently assign different data types to a particular column of a table in MySQL.