MySQL——将T1中的VARCHAR值插入T2中的DECIMAL字段

Question

在Table_1中，我从电子表格中导入了数据。在一个表列中有混合值：94.7、45.0、36、汽车、公共汽车。。。。等。数据类型为VARCHAR（10）

我想将Table_1中这一列的值插入Table_2中的DECIMAL（5,1）字段中。我希望非数字值注册为0.0。这很重要。

我已经尝试了几种方法，包括CAST（），它可以在我的MySQL GUI中工作，但会发出一长串警告。下面是我尝试过的SQL类型的一个示例。

DELETE FROM table_2;
INSERT INTO table_2 (NAME, decimal_column)
   (SELECT NAME
         , CAST(varchar_column AS DECIMAL(5,1))
      FROM table_1) ;

MySQL警告示例。

警告代码：1366小数不正确值：第1行的列"的

警告代码：1292截断DECIMAL值不正确：'house'

此外，PHP将没有它。当我尝试使用PHP运行相同的查询时，我得到了以下致命错误消息：

列的十进制值"不正确第-1行的"

感谢您的任何建议

Answer 1

如果要清除table_2，则可以使用可能更快的TRUNCATE table_2;。

实现这一点的一种方法（尽管我不知道它是否是最好的；对于大型数据集来说可能有点慢）是使用正则表达式：

INSERT INTO table_2 (NAME, decimal_column)
(SELECT NAME
     , IF(varchar_column REGEXP '^[[:digit:].]+$', varchar_column, 0.0)
  FROM table_1) ;

这是假设varchar_column的十进制值中没有空格。如果可能存在，那么您可以按如下方式使用TRIM：

IF(TRIM(varchar_column) REGEXP '^[[:digit:].]+$', TRIM(varchar_column), 0.0)

Answer 2

我尝试了以下方法，效果很好：

INSERT INTO `table_2` (`decimal_column`) SELECT CAST(`varchar_column` AS DECIMAL(5,1)) FROM `table_1`;

我使用的表有一个空白值、一个十进制值（作为VARCHAR）和单词"house"，所有内容都按预期进行了转换。

警告可以忽略。它本质上是MySQL告诉你它试图转换无法转换的数据。例如，单词"house"无法转换为十进制值，因此MySQL正在警告您（PHP也是如此）。您在MySQL GUI中没有看到错误的原因是，由于查询有效，错误被抑制了。

如果你想隐藏错误，你可以在PHP调用前面添加一个@符号（即@mysqli->query（'…'），使用ini_set（'display_errors'，false）禁用所有错误，或者使用error_reporting（e_NONE）。