mysql - 如何在mysql中选择创建表时指定数据类型？

Question

当我运行附加示例时，它会创建列 ID 和数据类型为 bigint(295) 的表我想将它定义为 int(11)。在更复杂的查询中(我从 group concat 中得到了 id)我得到的是文本而不是整数。

mysql中select如何在建表时指定数据类型？

DROP TABLE IF EXISTS some_table;
CREATE TABLE IF NOT EXISTS some_table AS (
SELECT
CONVERT("123,456,789,987,654,321,741,852,963,147,258,369,123,456,789,987,654,321,741,852,963,147,258,369
  ,2123,456,789,987,654,321,741,852,963,147,258,369,123,456,789,987,654,321,741,852,963,147,258,369
  ,3123,456,789,987,654,321,741,852,963,147,258,369,123,456,789,987,654,321,741,852,963,147,258,369"
, SIGNED INTEGER) AS id);

Answer 1

我找不到直接的方法来做到这一点。通过 alter table 解决它，工作了我，也许有更好的解决方案。

DROP TABLE IF EXISTS some_table;
CREATE TABLE IF NOT EXISTS some_table AS (
SELECT
CONVERT("123,456,789,987,654,321,741,852,963,147,258,369,123,456,789,987,654,321,741,852,963,147,258,369
  ,2123,456,789,987,654,321,741,852,963,147,258,369,123,456,789,987,654,321,741,852,963,147,258,369
  ,3123,456,789,987,654,321,741,852,963,147,258,369,123,456,789,987,654,321,741,852,963,147,258,369"
, SIGNED INTEGER) AS id);

ALTER TABLE some_table CHANGE id id INT( 11 ) NULL DEFAULT NULL ;