15

I know it is unorthodox and potentially dangerous to want to convert something from a larger to a smaller sized data type. However, in this case, it is extremely unlikely that the value of the BIGINT UNSIGNED column is ever larger than the maximum size of an INT column.

So, using MySQL, I'm reading the table structure. While reading the information_schema.columns.ordinal_position column, I realized that it is of type BIGINT UNSIGNED. Now, I want this as an INT instead for my processing purposes. I want to cast/convert the type in SQL.

CAST and CONVERT only allow me to change the sign of the data type, apparently.

SELECT CAST(ordinal_position AS SIGNED)
FROM information_schema.columns

I want the column specifically returned as an INT. E.g. chop the column at the maximum value of an INT and return that value.

For now I will just change the datatype after I pull the value back. But I'd like to know how to do it in SQL.

Josh M.
  • 26,437
  • 24
  • 119
  • 200

3 Answers3

9

this article seems to have a solution:

Create the function that will perform the conversion:

CREATE FUNCTION BigToInt (n BIGINT) RETURNS INTEGER RETURN n;

As you can see, the function is very short and simple: It takes a BIGINT and immediately returns it as an ordinary integer. However, one consequence of this is that some of the data will be truncated down to the largest possible value for Int.

(presumably you can add "UNSIGNED" to the signature - if not you can still combine it with the cast you already have that removes the unsigned part).

andrew cooke
  • 45,717
  • 10
  • 93
  • 143
  • 1
    What's the alternative way to do it without creating a function? Having to remember to teardown the function after use is a bother. – Pacerier Jan 26 '15 at 13:01
1

If you try to create the function that way, you will get this error:

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)

See this link for details on how to resolve it:

http://dev.mysql.com/doc/refman/5.0/en/stored-programs-logging.html

samayo
  • 16,163
  • 12
  • 91
  • 106
Dan Spiegel
  • 31
  • 2
-1

I unfortunately did not have the right to create functions on the DB where I was working, so after trying a few things this worked:

ALTER TABLE table_name MODIFY column_name INTEGER;

Saved me having to rewrite 15 foreign keys.

crdb
  • 31
  • 6