CAST as INT in MySQL: Examples and Usage

By /

Okay, here’s a comprehensive article on CAST as INT in MySQL, aiming for around 5000 words and covering various aspects, examples, and use cases:

CAST as INT in MySQL: A Deep Dive with Examples and Usage

Introduction

In the world of relational databases, data type conversion is a fundamental operation. MySQL, like other database management systems, provides powerful mechanisms for converting data from one type to another. One of the most frequently used conversion functions is CAST(). This article focuses specifically on using CAST() to convert values to the INT (integer) data type in MySQL. We’ll explore the syntax, behavior, nuances, error handling, performance considerations, and numerous practical examples to give you a complete understanding of this essential function.

1. Understanding the INT Data Type Family in MySQL

Before diving into CAST(), it’s crucial to understand the various integer data types available in MySQL. This context is essential for making informed decisions about when and how to use CAST( ... AS INT). MySQL offers a range of integer types, each with different storage sizes and value ranges:

  • TINYINT:
    • Storage: 1 byte
    • Signed Range: -128 to 127
    • Unsigned Range: 0 to 255
  • SMALLINT:
    • Storage: 2 bytes
    • Signed Range: -32,768 to 32,767
    • Unsigned Range: 0 to 65,535
  • MEDIUMINT:
    • Storage: 3 bytes
    • Signed Range: -8,388,608 to 8,388,607
    • Unsigned Range: 0 to 16,777,215
  • INT (or INTEGER):
    • Storage: 4 bytes
    • Signed Range: -2,147,483,648 to 2,147,483,647
    • Unsigned Range: 0 to 4,294,967,295
  • BIGINT:
    • Storage: 8 bytes
    • Signed Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
    • Unsigned Range: 0 to 18,446,744,073,709,551,615

Key Considerations:

  • Signed vs. Unsigned: The SIGNED and UNSIGNED attributes determine whether the integer can represent negative values (SIGNED) or only non-negative values (UNSIGNED). The UNSIGNED attribute doubles the maximum positive value that can be stored.
  • Storage Size: The storage size directly impacts the range of values that can be represented. Choosing the smallest integer type that can accommodate your data is crucial for efficient storage and performance. Using a BIGINT when a TINYINT would suffice wastes storage space.
  • Default: When you use CAST(... AS INT), without specifying SIGNED or UNSIGNED, MySQL defaults to SIGNED INT.
  • INTEGER is a synonym for INT: They are interchangeable in MySQL.

2. The CAST() Function: Syntax and Basic Usage

The CAST() function in MySQL provides a way to explicitly convert an expression from one data type to another. The general syntax is:

sql
CAST(expression AS datatype)

  • expression: This is the value or column you want to convert. It can be a literal value (like a string or number), a column from a table, or a more complex expression.
  • datatype: This specifies the target data type to which you want to convert the expression. For integer conversion, this will be one of the integer types discussed above (e.g., INT, TINYINT, BIGINT, INT UNSIGNED, etc.).

Basic Examples:

“`sql
— Convert a string to an integer
SELECT CAST(‘123’ AS INT); — Result: 123 (as an integer)

— Convert a floating-point number to an integer (truncation occurs)
SELECT CAST(123.456 AS INT); — Result: 123

— Convert a date to an integer (representation depends on the date format)
SELECT CAST(‘2023-10-27’ AS INT); — Result: 20231027

— Convert a boolean (TRUE/FALSE) to an integer
SELECT CAST(TRUE AS INT); — Result: 1
SELECT CAST(FALSE AS INT); — Result: 0

— Using CAST with a column
SELECT CAST(price AS INT) AS integer_price FROM products;
“`

3. Converting Different Data Types to INT

Let’s explore how CAST(... AS INT) behaves when converting various data types:

3.1. String to INT

This is perhaps the most common use case. MySQL attempts to parse the string as an integer.

  • Successful Conversion: If the string contains a valid integer representation (e.g., ‘123’, ‘-45’, ‘0’), the conversion is straightforward.

    sql
    SELECT CAST('123' AS INT); -- Result: 123
    SELECT CAST('-45' AS INT); -- Result: -45

  • Leading and Trailing Spaces: MySQL ignores leading and trailing whitespace.

    sql
    SELECT CAST(' 123 ' AS INT); -- Result: 123

  • Non-Numeric Characters:

    • Leading Non-Numeric Characters: If the string starts with non-numeric characters (other than a leading ‘+’ or ‘-‘), the result is usually 0.

      sql
      SELECT CAST('abc123' AS INT); -- Result: 0
      SELECT CAST('!@#123' AS INT); -- Result: 0

    • Embedded Non-Numeric Characters: If the string starts with a valid number but contains non-numeric characters later, MySQL converts the leading numeric part and ignores the rest.

      sql
      SELECT CAST('123abc456' AS INT); -- Result: 123
      SELECT CAST('123.456abc' AS INT); -- Result: 123
      * Empty String: Casting an empty string to INT results 0.
      sql
      SELECT CAST('' AS INT); -- Result: 0

  • Scientific Notation: MySQL handles strings in scientific notation.

    sql
    SELECT CAST('1.23e2' AS INT); -- Result: 123
    SELECT CAST('1.23e-2' AS INT); -- Result: 0 (due to truncation)

3.2. Floating-Point Numbers (FLOAT, DOUBLE) to INT

When casting floating-point numbers to INT, MySQL truncates the decimal portion. It does not round to the nearest integer.

sql
SELECT CAST(123.456 AS INT); -- Result: 123
SELECT CAST(123.999 AS INT); -- Result: 123
SELECT CAST(-123.456 AS INT); -- Result: -123

3.3. Date and Time Types (DATE, DATETIME, TIMESTAMP) to INT

The result of casting date and time types to INT depends on the context and the implicit format MySQL uses for conversion. It’s generally less common to cast dates directly to INT, but it can be useful in specific scenarios. Usually, you’ll want more control over the conversion using date and time functions (see section 6).

“`sql
— DATE to INT (YYYYMMDD format)
SELECT CAST(‘2023-10-27’ AS INT); — Result: 20231027

— DATETIME to INT (YYYYMMDDHHMMSS format – but often truncated)
SELECT CAST(‘2023-10-27 14:30:00’ AS INT); — Result: 20231027 (time part is lost)

— TIMESTAMP to INT (similar to DATETIME)
SELECT CAST(NOW() AS INT); — Result: Similar to YYYYMMDD, but may include time depending on context
“`

Important Note: Casting DATETIME or TIMESTAMP to INT can lead to data loss (the time component is typically truncated). For precise date/time manipulation, use the dedicated date and time functions.

3.4. Boolean (BOOL, BOOLEAN) to INT

MySQL treats TRUE as 1 and FALSE as 0 when casting to INT.

“`sql
SELECT CAST(TRUE AS INT); — Result: 1
SELECT CAST(FALSE AS INT); — Result: 0

— Using a boolean expression
SELECT CAST(10 > 5 AS INT); — Result: 1 (TRUE)
SELECT CAST(10 < 5 AS INT); — Result: 0 (FALSE)
“`

3.5. Other Integer Types to INT

You can cast between different integer types (e.g., TINYINT to INT, BIGINT to INT).

  • Widening Conversion (e.g., TINYINT to INT): This is generally safe and lossless, as the target type (INT) has a larger range than the source type (TINYINT).

    sql
    SELECT CAST(127 AS TINYINT); --Store 127 in a TINYINT variable.
    SELECT CAST(127 AS INT); -- Result: 127
    * Narrowing Conversion (e.g., BIGINT to INT): This can lead to data loss if the BIGINT value is outside the range of INT. MySQL will truncate the value, potentially leading to unexpected results. This is an important case for error handling.

    sql
    SELECT CAST(9223372036854775807 AS BIGINT); --Store the BIGINT max value.
    SELECT CAST(9223372036854775807 AS INT); -- Result: -1 (Data Loss and overflow!)
    SELECT CAST(3000000000 AS INT UNSIGNED); -- Result: 3000000000 (Within unsigned range)
    SELECT CAST(5000000000 AS INT UNSIGNED); -- Result: 4294967295 (Overflow, capped at max unsigned int)

4. Error Handling and Overflow

When using CAST(... AS INT), it’s crucial to be aware of potential errors and data loss, especially during narrowing conversions or when dealing with strings that cannot be parsed as integers.

4.1. Data Loss (Narrowing Conversion)

As shown in the previous example, casting a BIGINT with a value larger than the maximum INT value results in truncation. The most significant bits are discarded, leading to an incorrect result.

4.2. Invalid String Input

If you attempt to cast a string that cannot be interpreted as a valid integer, MySQL typically returns 0. While this prevents an error from stopping the query execution, it can mask underlying data issues.

sql
SELECT CAST('invalid' AS INT); -- Result: 0

4.3. Strict SQL Mode

MySQL’s SQL mode affects how it handles invalid or out-of-range values. The STRICT_TRANS_TABLES and STRICT_ALL_TABLES modes are particularly relevant.

  • Non-Strict Mode (Default in Older Versions): MySQL may issue warnings but still perform the conversion, potentially with data loss or unexpected results (like converting ‘invalid’ to 0).
  • Strict Mode: In strict mode, MySQL will generate an error and stop the query execution if it encounters an invalid value during the CAST operation. This is generally the preferred behavior for data integrity.

Enabling Strict Mode:

You can set the SQL mode globally (for all connections) or at the session level (for the current connection).

“`sql
— Set strict mode for the current session
SET SESSION sql_mode = ‘STRICT_TRANS_TABLES’;

— Set strict mode globally (requires SUPER privilege)
SET GLOBAL sql_mode = ‘STRICT_TRANS_TABLES’;
“`

Example (Strict Mode):

sql
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
SELECT CAST('invalid' AS INT); -- Error: Incorrect integer value: 'invalid' for column ...
SELECT CAST(9223372036854775807 AS INT); -- Error: Out of range value for column ...

4.4. Using IF and CASE for Conditional Casting

To handle potential errors gracefully, you can combine CAST() with conditional logic using IF or CASE statements. This allows you to check the input value before attempting the conversion.

“`sql
— Example using IF:
SELECT
IF(
my_column REGEXP ‘^[+-]?[0-9]+$’, — Check if it’s a valid integer string
CAST(my_column AS INT),
NULL — Or handle the error in another way
) AS converted_value
FROM my_table;

— Example using CASE:
SELECT
CASE
WHEN my_column REGEXP ‘^[+-]?[0-9]+$’ THEN CAST(my_column AS INT)
WHEN my_column IS NULL THEN NULL
ELSE -1 — Indicate an error with a specific value
END AS converted_value
FROM my_table;
“`

Explanation:

  • REGEXP '^[+-]?[0-9]+$': This regular expression checks if the string (my_column) consists of:

    • ^: Beginning of the string
    • [+-]?: An optional plus or minus sign.
    • [0-9]+: One or more digits.
    • $: End of the string.

  • The IF or CASE statement checks if the regular expression matches. If it does, the CAST is performed. Otherwise, a different value (like NULL or -1) is returned, allowing you to handle the invalid input separately.

4.5. Using STR_TO_DATE() for Controlled Date Conversion

If you are attempting to convert a date in string form to INT, you might use CAST() in combination with the STR_TO_DATE() function. This allows you to handle cases where the date might not be in the default MySQL format.

“`sql
–Convert a date string to an integer in YYYYMMDD format, with error handling.

SELECT
CASE
WHEN STR_TO_DATE(date_string, ‘%Y-%m-%d’) IS NOT NULL THEN
CAST(REPLACE(date_string, ‘-‘, ”) AS UNSIGNED)
ELSE
NULL — Or some other error handling
END AS integer_date
FROM your_table;
“`

5. Performance Considerations

While CAST() is a versatile function, it’s important to consider its performance implications, especially when used in queries that process large datasets.

  • Index Usage: CAST() can prevent MySQL from using indexes effectively. If you have an index on a column and you CAST() that column in a WHERE clause, MySQL might not be able to use the index for filtering.

    “`sql
    — Index on date_column might not be used:
    SELECT * FROM my_table WHERE CAST(date_column AS INT) = 20231027;

    — Better: Use date functions for filtering
    SELECT * FROM my_table WHERE date_column = ‘2023-10-27’;
    “`

  • Data Type Compatibility: Casting between compatible data types (e.g., TINYINT to INT) is generally faster than casting between incompatible types (e.g., string to INT). The latter requires parsing and validation, which adds overhead.

  • Materialized Views/Generated Columns: If you frequently need to perform the same CAST operation, consider creating a materialized view or a generated column. This pre-computes the converted value and stores it, avoiding repeated calculations.

    sql
    -- Generated column (MySQL 5.7+)
    ALTER TABLE my_table
    ADD COLUMN integer_price INT AS (CAST(price AS INT)) STORED;
    * Benchmarking: It’s good practice to perform benchmarks in your particular environment, to see if the performance differences are significant.

6. Advanced Usage and Alternatives

6.1. CONVERT() Function

MySQL also provides the CONVERT() function, which is closely related to CAST(). For simple type conversions like INT, they are often interchangeable.

sql
SELECT CONVERT('123', INT); -- Equivalent to CAST('123' AS INT)

The CONVERT() function has an additional syntax for character set conversions, which CAST() does not support. However, for converting to INT, CAST() is generally preferred for readability because of its more SQL-standard syntax.

6.2. Implicit Type Conversion

MySQL performs implicit type conversion in certain situations. For example, if you add a string to an integer, MySQL will attempt to convert the string to a number.

sql
SELECT 1 + '2'; -- Result: 3 (Implicit conversion of '2' to an integer)

While implicit conversion can be convenient, it’s generally best to use explicit conversion (CAST() or CONVERT()) for clarity and to avoid unexpected behavior. Relying on implicit conversion can make your code harder to understand and maintain.

6.3. Floor, Ceiling and Round

When converting from floating point numbers to integers, consider if FLOOR(), CEILING(), or ROUND() might be better choices than CAST(), which truncates.

sql
SELECT FLOOR(3.7); -- 3 (Largest integer not greater than the argument)
SELECT CEILING(3.1); -- 4 (Smallest integer not less than the argument)
SELECT ROUND(3.14); -- 3 (Rounds to the nearest integer)
SELECT ROUND(3.14, 1); -- 3.1 (Rounds to 1 decimal place)

6.4. Date and Time Functions

For converting date and time values, it’s often more appropriate to use dedicated functions rather than CAST(... AS INT).

  • YEAR(), MONTH(), DAY(): Extract specific parts of a date.
  • UNIX_TIMESTAMP(): Convert a date/time to a Unix timestamp (seconds since the epoch).
  • DATE_FORMAT(): Format a date/time value as a string (and then potentially CAST() to INT if needed, after careful formatting).

“`sql
— Extract the year as an integer
SELECT YEAR(‘2023-10-27’); — Result: 2023

— Convert to a Unix timestamp (integer)
SELECT UNIX_TIMESTAMP(‘2023-10-27 14:30:00’); — Result: Number of seconds since 1970-01-01

— Format as YYYYMMDD and then cast to INT
SELECT CAST(DATE_FORMAT(‘2023-10-27’, ‘%Y%m%d’) AS UNSIGNED); — Result: 20231027 (more controlled)
“`

7. Practical Examples and Use Cases

Let’s look at some more practical examples to illustrate how CAST(... AS INT) is used in real-world scenarios.

7.1. Data Cleaning and Transformation

Suppose you have a table with a price column stored as a VARCHAR (string) because the data source sometimes included currency symbols or commas. You want to clean this data and convert it to an INT for calculations.

“`sql
— Sample data
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(255),
price VARCHAR(50)
);

INSERT INTO products (id, name, price) VALUES
(1, ‘Product A’, ‘12.99’),
(2, ‘Product B’, ‘$25’),
(3, ‘Product C’, ‘1,000’),
(4, ‘Product D’, ‘invalid’),
(5, ‘Product E’, ’99’);

— Data cleaning and conversion
UPDATE products
SET price = REPLACE(price, ‘$’, ”); — Remove dollar signs

UPDATE products
SET price = REPLACE(price, ‘,’, ”); — Remove commas

— Convert to INT, handling errors
ALTER TABLE products
ADD COLUMN integer_price INT;

UPDATE products
SET integer_price = CASE
WHEN price REGEXP ‘^[0-9.]+$’ THEN CAST(price AS UNSIGNED)
ELSE NULL — Or another error handling strategy
END;

SELECT * FROM products;
“`

7.2. Calculating Age from a Birthdate (String Format)

Assume you have a table with birthdates stored as strings in ‘YYYY-MM-DD’ format. You want to calculate the approximate age.

“`sql
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
birthdate_str VARCHAR(10) — Stored as YYYY-MM-DD
);

INSERT INTO users (name, birthdate_str) VALUES
(‘Alice’, ‘1990-05-15’),
(‘Bob’, ‘1985-12-20’),
(‘Charlie’, ‘2000-01-10’);
–calculate the age in years
SELECT
name,
birthdate_str,
FLOOR(
(CAST(DATE_FORMAT(NOW(), ‘%Y%m%d’) AS UNSIGNED) – CAST(REPLACE(birthdate_str, ‘-‘, ”) AS UNSIGNED)) / 10000
) AS approximate_age
FROM users;

``
This example first usesREPLACEto remove hyphens. Then usesCASTto convert both today's date, formatted to 'YYYYMMDD', and the date of birth to unsigned integers. This allows us to subtract them, giving us a difference. That difference is divided by 10000 to give a first order approximation of the age. Lastly,FLOOR` is used to truncate the decimal and get just the whole year value of the age.

7.3. Working with Bit Flags (Less Common with INT, but Possible)

While MySQL has a BIT data type, you might occasionally encounter situations where integer columns are used to store bit flags (each bit represents a boolean flag). CAST() can help with manipulating these flags. This is generally less ideal than using the BIT type and bitwise operators.

“`sql
— Example: Each bit in the ‘permissions’ column represents a permission.
CREATE TABLE user_permissions (
user_id INT PRIMARY KEY,
permissions INT UNSIGNED
);

INSERT INTO user_permissions (user_id, permissions) VALUES
(1, 5); — Binary: 0101 (Read and Execute permissions)

— Check if the user has the ‘Read’ permission (bit 0)
SELECT
user_id,
CAST((permissions & 1) AS UNSIGNED) AS has_read_permission –Bitwise AND
FROM user_permissions
WHERE user_id = 1;
“`

7.4. Importing Data from CSV or Other External Sources

When importing data from external sources (like CSV files), you often need to convert string columns to their appropriate data types, including INT.

“`sql
— Assuming you’ve loaded data into a temporary table with all columns as VARCHAR
CREATE TEMPORARY TABLE temp_products (
id VARCHAR(255),
name VARCHAR(255),
price VARCHAR(255)
);

— … (Load data into temp_products) …

— Create the final table
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(255),
price INT
);

— Insert data, converting price to INT
INSERT INTO products (id, name, price)
SELECT
CAST(id AS INT),
name,
CASE
WHEN price REGEXP ‘^[0-9]+$’ THEN CAST(price AS INT)
ELSE NULL — Handle invalid price values
END
FROM temp_products;
“`

8. Conclusion

The CAST(... AS INT) function in MySQL is a fundamental tool for data type conversion. Understanding its behavior, limitations, and potential pitfalls is essential for writing robust and efficient SQL queries. Remember to:

  • Choose the appropriate integer type (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, SIGNED, UNSIGNED) based on your data’s range.
  • Handle potential errors due to invalid string input or narrowing conversions, especially when working with user-provided data or external sources. Consider using strict SQL mode.
  • Use conditional logic (IF, CASE) and regular expressions to validate input before casting.
  • Be mindful of performance implications, especially regarding index usage. Consider alternatives like generated columns or materialized views for frequently used conversions.
  • Utilize date and time functions when working with date/time values for better control and accuracy.
  • Use FLOOR(), CEILING(), and ROUND() when you want different rounding behavior from the truncation that CAST() does.

By mastering CAST(... AS INT) and its related concepts, you’ll be well-equipped to handle a wide variety of data manipulation tasks in MySQL. This comprehensive guide should provide a solid foundation for effectively using this crucial function in your database work.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top