Convert BIGINT UNSIGNED to INT

Question

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.

Answer 1

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).