4

If I want to take a string and create a table (this fails in strict mode),

CREATE TABLE g
  AS SELECT CAST('2147483699' AS int);

How do I tell it that I want a bigint type? I'll also get an int(10) type. SELECTing from it is humorous too,

+---------------------------+
| CAST('2147483699' AS int) |
+---------------------------+
|                2147483647 |
+---------------------------+

The data is actually wrong, and it silently failed. I know that's a known issue with this database having really poor defaults, but I how can I create the table as a bigint?

CREATE TABLE g
  AS SELECT CAST('2147483699' AS bigint);

Doing that I get this error,

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

How do I cast an interger to a bigint?

PostgreSQL

With PostgreSQL, it's pretty simple. In addition to throwing an exception if you cast to an int, you cast to a bigint using

SELECT CAST('2147483699' AS bigint);
    int8    
------------
 2147483699
1
  • It seems like the size for the integer is determined by the length of the string. Had a few laughs figuring this one out... Long story short, try CREATE TABLE g AS SELECT cast(lpad('2147483699', 20, '0') AS unsigned);. That gives me a bigint(20) for i in g on a MySQL 5.7. But I don't know if it's any good for MariaDB.
    – sticky bit
    May 2, 2018 at 23:37

3 Answers 3

4

Silently? Let's try:

CREATE TABLE g
   AS SELECT CAST('2147483699' AS int);

MariaDB 10.1.32, default sql_mode (non-strict)

Query OK, 1 row affected, 1 warning (0.19 sec) Records: 1 Duplicates: 0 Warnings: 1

And the warning is:

| Warning | 1264 | Out of range value for column 'CAST('2147483699' AS int)' at row 1 |

Yes, selecting from the table is interesting, but not surprising given the warning.

(I don't have a MariaDB 10.2 instance at the moment, but testing on dbfiddle.uk indicates it too fails, though again not silently.)

MariaDB 10.3.6, default sql_mode (strict)

No warnings.

select * from g;

Gives:

+---------------------------+
| CAST('2147483699' AS int) |
+---------------------------+
|                2147483699 |
+---------------------------+
1 row in set (0.000 sec)

And the column is indeed created as bigint(10).

MySQL 8.0, both the default strict and non-strict sql_mode

ERROR 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 'int)' at line 2

Computer says no.

Conclusion

Can MariaDB or MySQL cast as bigint? As we have seen, yes, MariaDB 10.3 can (though it's not GA yet ... but surely any day now it will be). However, it can only cast as bigint implicitly. You can't explicitly cast as bigint, just like you can't cast as varchar (but see MDEV-11283), text, float, tinyint etcetera. There are only a limited number of datatypes you can cast to. This is not well documented, but CAST appears to support only the same datatypes as listed for the CONVERT function.

1

A method that works both in MySQL (at least 5.6+) and MariaDB (at least 10.2+), bypassing the need for casting:

CREATE TABLE g (b bigint)
  AS 
SELECT 2147483699 AS b            -- no CAST needed
UNION ALL
SELECT '2147483699' ;

Tests in:

0

How about converting the string to numeric so that you can do arithmetic, then do harmless arithmetic:

SELECT '2147483699'+0;

+----------------+
| '2147483699'+0 |
+----------------+
|     2147483699 |
+----------------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.