将BIGINT UNSIGNED转换为INT [英] Convert BIGINT UNSIGNED to INT

问题描述

我知道要将某些内容从较大的数据类型转换为较小的数据类型是不合常规的，并且有潜在的危险.但是，在这种情况下，BIGINT UNSIGNED列的值极不可能大于INT列的最大大小.

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.

因此，使用MySQL，我正在读取表结构.在阅读information_schema.columns.ordinal_position列时，我意识到它的类型为BIGINT UNSIGNED.现在，出于处理目的，我希望将其作为INT.我想在SQL中强制转换/转换类型.

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和CONVERT显然仅允许我更改数据类型的符号.

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

我希望该列专门作为INT返回.例如.将列以INT的最大值截断并返回该值.

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.

现在，将值拉回后，我将只更改数据类型.但是我想知道如何在SQL中做到这一点.

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.

推荐答案

本文似乎有解决方案:

创建将执行转换的函数:

Create the function that will perform the conversion:

创建函数BigToInt(n BIGINT)返回整数返回n;

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

如您所见，该函数非常简短: BIGINT并立即将其作为普通整数返回.但是，一个 其结果是一些数据将被截断为 Int的最大可能值.

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.

(大概您可以在签名中添加"UNSIGNED"-如果没有，您仍然可以将其与已经删除了未签名部分的演员表结合使用).

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

这篇关于将BIGINT UNSIGNED转换为INT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！