mysql - 动态确定列的最合适的数据类型

Question

好的，我知道这看起来很奇怪。但是，我正在尝试弄清楚如何使用 MySql 数据库中的 SQL 查询来动态确定将列设置为哪种数据类型。

我有许多通过脚本动态创建的表。我最初创建它们，并将所有列的大小设置为长度为 2000 的“文本”数据类型。我这样做是为了不必担心在插入过程中实际进入表的内容。为表格插入完数据后，我将浏览表格，检查存储数据的最大长度以及为每一列和表格存储的数据类型。有很多表和很多列。我已经想出了一个过程 - 但是，我有点担心它会在这些时候错过一些东西。该数据库总共有大约 1250 多个表和大约 300 多 百万行。真正不幸的是，数据类型永远无法保证一致。曾经。这就是为什么我必须在导入后处理它们。

基本上，我运行查询以获取列的 MAX CHAR_LENGTH 并将其存储。然后，我运行一个查询，在数据库中选择 N 条记录，然后检查它们是数字还是字符。我知道我知道。这不是解决问题的最佳方式。

因为我绝对无法确定传入的数据(真的是一场噩梦)，所以我想知道是否有办法查询列以查看其中包含哪种数据？我不是在寻找一个查询来查看该列是如何定义的(显然，因为我已经用“通用”类型的“文本”定义了它)。

我知道我可以在导入过程中在脚本中处理这个问题，但我希望只运行一个 ALTER TABLE 命令，而不是编写一堆额外的代码。

非常感谢任何想法或想法!

Answer 1

使用 PROCEDURE ANALYSE()选项:

SELECT * FROM `tablename` PROCEDURE ANALYSE();

这是一个输出示例:

mysql> select * from zip procedure analyse() \G
*************************** 1. row ***************************
             Field_name: zip.zip
              Min_value: 00501
              Max_value: 99950
             Min_length: 5
             Max_length: 5
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 5.0000
                    Std: NULL
      Optimal_fieldtype: MEDIUMINT(5) UNSIGNED NOT NULL
*************************** 2. row ***************************
             Field_name: zip.city
              Min_value: Aaronsburg
              Max_value: Zwolle
             Min_length: 3
             Max_length: 26
       Empties_or_zeros: 1009
                  Nulls: 0
Avg_value_or_avg_length: 8.3869
                    Std: NULL
      Optimal_fieldtype: VARCHAR(26) NOT NULL
*************************** 3. row ***************************
             Field_name: zip.state_id
              Min_value: AA
              Max_value: WY
             Min_length: 2
             Max_length: 2
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 2.0000
                    Std: NULL
      Optimal_fieldtype: ENUM('AA','AE','AK','AL','AP','AR','AS','AZ','CA','CO','CT','DC','DE','FL','FM','GA','GU','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MH','MI','MN','MO','MP','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','PR','PW','RI','SC','SD','TN','TX','UT','VA','VI','VT','WA','WI','WV','WY') NOT NULL
*************************** 4. row ***************************
             Field_name: zip.latitude
              Min_value: -7.209975
              Max_value: 71.299525
             Min_length: 2
             Max_length: 8
       Empties_or_zeros: 1009
                  Nulls: 0
Avg_value_or_avg_length: 37.599173975674866
                    Std: 7.949323125673274
      Optimal_fieldtype: FLOAT NOT NULL
*************************** 5. row ***************************
             Field_name: zip.longitude
              Min_value: -176.63675
              Max_value: -64.734694
             Min_length: 3
             Max_length: 8
       Empties_or_zeros: 1009
                  Nulls: 0
Avg_value_or_avg_length: -88.79028976104503
                    Std: 20.6017874416888
      Optimal_fieldtype: FLOAT NOT NULL
*************************** 6. row ***************************
             Field_name: zip.timezone_id
              Min_value: 3
              Max_value: 11
             Min_length: 1
             Max_length: 2
       Empties_or_zeros: 0
                  Nulls: 1009
Avg_value_or_avg_length: 8.1563
                    Std: 1.0430
      Optimal_fieldtype: ENUM('3','5','6','7','8','9','11')
*************************** 7. row ***************************
             Field_name: zip.dst
              Min_value: 1
              Max_value: 1
             Min_length: 1
             Max_length: 1
       Empties_or_zeros: 2618
                  Nulls: 0
Avg_value_or_avg_length: 0.9376
                    Std: 0.2419
      Optimal_fieldtype: ENUM('0','1') NOT NULL
*************************** 8. row ***************************
             Field_name: zip.status
              Min_value: Active
              Max_value: Active
             Min_length: 6
             Max_length: 6
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 6.0000
                    Std: NULL
      Optimal_fieldtype: ENUM('Active') NOT NULL
*************************** 9. row ***************************
             Field_name: zip.created
              Min_value: 2010-09-25 11:43:41
              Max_value: 2011-05-13 15:56:00
             Min_length: 19
             Max_length: 19
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 19.0000
                    Std: NULL
      Optimal_fieldtype: ENUM('2010-09-25 11:43:41','2010-09-25 11:43:56','2010-11-01 09:49:32','2011-05-13 15:56:00') NOT NULL
*************************** 10. row ***************************
             Field_name: zip.updated
              Min_value: 2010-09-24 23:13:41
              Max_value: 2011-05-13 15:56:00
             Min_length: 19
             Max_length: 19
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 19.0000
                    Std: NULL
      Optimal_fieldtype: ENUM('2010-09-24 23:13:41','2010-09-24 23:13:56','2010-09-29 12:40:56','2010-11-01 09:49:32','2011-05-13 15:56:00') NOT NULL
10 rows in set (0.15 sec)