mysql convert int

MySQL is an open source relational database management system that supports multiple data types and provides powerful query and data processing functions. In MySQL, integers are a basic data type that can be used for various operations and comparisons. However, sometimes we need to convert a string or other data type to an integer in order to perform calculations or comparisons. This article will introduce the integer conversion functions and usage in MySQL.

MySQL integer data type

In MySQL, there are many data types for integers, including:

• TINYINT: ranging from -128 to 127 Signed integer.
• SMALLINT: A signed integer in the range -32768 to 32767.
• MEDIUMINT: A signed integer in the range -8388608 to 8388607.
• INT or INTEGER: A signed integer in the range -2147483648 to 2147483647.
• BIGINT: A signed integer in the range -9223372036854775808 to 9223372036854775807.

In MySQL, the default length of an integer field is usually 11 characters, including the sign bit. If a larger integer range is required, you can use the UNSIGNED parameter to specify an unsigned integer.

MySQL integer conversion function

MySQL provides a variety of integer conversion functions, making it easy to convert other data types into integers when executing SQL statements. These functions include:

• CAST(expr AS type): Converts the expression expr to the specified type type, where type can be TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT.
• CONVERT(expr, type): Similar to CAST, converts the expression expr to the specified type type.
• ABS(x): Returns the absolute value of x.
• CEILING(x) or CEIL(x): Returns the smallest integer that is not less than x.
• FLOOR(x): Returns the largest integer not greater than x.
• ROUND(x, d): Round x to d decimal places, defaults to 0 if d is omitted.

The following are some examples of integer conversion:

• Convert the string '123' to an integer type: SELECT CAST('123' AS INT);
• Convert the string '3.14' to an integer type, the result is 3: SELECT CAST('3.14' AS INT);
• Convert the string '3.9' to an integer type, the result is 3: SELECT CAST('3.9' AS INT);
• Convert the floating point number 3.14 to the integer type, the result is 3: SELECT CAST(3.14 AS INT);
• Convert the floating point number 3.9 to the integer type , the result is 3: SELECT CAST(3.9 AS INT);
• Convert the negative number -3.14 to an integer type, the result is -3: SELECT CAST(-3.14 AS INT);
• Convert the character The string 'abc' is converted to an integer type, and the result is 0: SELECT CAST('abc' AS INT);
• Convert NULL to an integer type, and the result is NULL: SELECT CAST(NULL AS INT);

It should be noted that if a non-numeric string is converted to an integer type, the result will automatically become 0. If you need to determine whether a string is a number, you can use MySQL's own function:

• ISNUMERIC(expr): If expr is a number, return 1, otherwise return 0. The implementation of this function in MySQL is relatively complicated, please refer to the official documentation.

Summary

In MySQL, integer is a basic data type that can be used for various operations and comparisons. If you need to convert other data types to integers, you can use the integer conversion functions provided by MySQL such as CAST, CONVERT, ABS, CEILING, FLOOR, ROUND, etc. Note that if you convert a non-numeric string to an integer type, the result becomes 0. In actual development, it is necessary to select an appropriate integer conversion function according to the business scenario in order to perform data processing and analysis more efficiently.

The above is the detailed content of mysql convert int. For more information, please follow other related articles on the PHP Chinese website!