If you’re getting error number 1064 that reads something like “1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘INTEGER )’ at line 1“, it could be that you’re using the wrong term for the integer.

MySQL doesn’t let us use just the INTEGER keyword when converting to an integer. We need to use either SIGNED or UNSIGNED, optionally followed by INTEGER or INT. When we do this, it produces a signed or unsigned BIGINT value.

This behaviour is slightly different to MariaDB, where INTEGER is short for SIGNED INTEGER, but we also have the option of explicitly stating SIGNED or UNSIGNED.

If you think this is the reason you’re getting the error, you can fix it by using the correct keyword when referring to the type.

Example of Error

Here’s an example of code that produces the error:

SELECT CAST( 123.45 AS INTEGER );

Result:

1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTEGER )' at line 1

We get the same error when using the CONVERT() function:

SELECT CONVERT( 123.45, INTEGER );

Result:

1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTEGER )' at line 1

As mentioned, we can’t use just INTEGER when converting to an integer. We need to specify whether it’s signed or unsigned.

Solution

The problem is easily solved by using the correct keyword – either SIGNED or UNSIGNED, optionally followed by INTEGER or INT.

So to fix the above error, we can prefix INTEGER with SIGNED:

SELECT CAST( 123.45 AS SIGNED INTEGER );

Result:

123

We can alternatively omit the INTEGER part:

SELECT CAST( 123.45 AS SIGNED );

Result:

123

Or we can use SIGNED INT instead of SIGNED INTEGER:

SELECT CAST( 123.45 AS SIGNED INT );

Result:

123

The same applies to the CONVERT() function:

SELECT 
    CONVERT( '123.45', SIGNED INTEGER ),
    CONVERT( '123.45', SIGNED INT ),
    CONVERT( '123.45', SIGNED );

Result:

+-------------------------------------+---------------------------------+-----------------------------+
| CONVERT( '123.45', SIGNED INTEGER ) | CONVERT( '123.45', SIGNED INT ) | CONVERT( '123.45', SIGNED ) |
+-------------------------------------+---------------------------------+-----------------------------+
|                                 123 |                             123 |                         123 |
+-------------------------------------+---------------------------------+-----------------------------+
1 row in set, 3 warnings (0.00 sec)

In this case I got three warnings.

Let’s check the warnings:

SHOW WARNINGS;

Result:

+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '123.45' |
| Warning | 1292 | Truncated incorrect INTEGER value: '123.45' |
| Warning | 1292 | Truncated incorrect INTEGER value: '123.45' |
+---------+------+---------------------------------------------+

MySQL warned us that the value was truncated when converted to the INTEGER type. Integers don’t have a fractional part, and that’s the part that was removed. In this case it’s not an issue and it’s to be expected, given the value I provided.

Signed vs Unsigned Integers

When deciding whether to use a signed integer or unsigned, we need to consider the data that we’ll be dealing with.

Here’s a summary of the difference between signed integers and unsigned integers:

• Signed integers can represent both positive and negative numbers. For example, a signed integer can represent 0, -25 and 25.
• Unsigned integers can only represent non-negative numbers (i.e. zero and positive numbers). For example, an unsigned integer can represent 0 and 25, but not -25.

See Signed vs Unsigned Integers for more information on the difference between signed and unsigned integers.