mysql - 按列类型更新表

Question

我有一个包含敏感数字的大型 MYSQL 数据表。我们需要一家外部公司为我们制作一个可以使用数据的应用程序。我们不想给他们真实的数字。相反，我想伪造数据。这是我正在尝试的

示例表

Name<varChar> | col1 <int>| col2 <decimal>| coln <int>
Joe           |  1        |  2            |  3

我想编写一个脚本，通过获取当前实际值并将其乘以大于 ZERO 的随机数来更新所有整数或小数列(基本上是数字)。

这是我编写的正确轨道上的示例代码块。

UPDATE TABLE SET VALUE=VALUE*ROUND(RAND());

但缺少以下内容:

1) 只更新一列

2) 不验证列是否为数字数据

有人可以帮助这个 sql 脚本吗。

Answer 1

这是一个仅适用于 mysql 的解决方案。您可以按类型查询 information_schema 表中的列，然后使用变量构造您的 sql 查询。

mysql> CREATE TABLE example (
    ->          id INT,
    ->          data VARCHAR(100),
    ->          id2 INT,
    ->          id3 decimal,
    ->          id4 double,
    ->          id5 float,
    ->          id6 numeric   
    ->        );
Query OK, 0 rows affected (0.10 sec)

mysql> insert into example values (1,'hello',2,3,4,5,6);
Query OK, 1 row affected (0.05 sec)

mysql> SET @sql = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(CONCAT(column_name,'=ROUND(',column_name,'*RAND())'))
    -> INTO @sql
    -> FROM information_schema.columns 
    -> WHERE table_schema = 'test' -- your db name
    -> AND table_name = 'example' 
    -> AND (column_type LIKE  '%int%'
    -> OR column_type LIKE '%decimal%'
    -> OR column_type LIKE '%numeric%'
    -> OR column_type LIKE '%float%'
    -> OR column_type LIKE '%double%');
Query OK, 1 row affected (0.00 sec)

mysql> SET @sql1 = CONCAT("UPDATE example SET ",@sql);
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM @sql1;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from example;
+------+-------+------+------+------+------+------+
| id   | data  | id2  | id3  | id4  | id5  | id6  |
+------+-------+------+------+------+------+------+
|    0 | hello |    0 |    3 |    4 |    1 |    3 |
+------+-------+------+------+------+------+------+
1 row in set (0.00 sec)

mysql> select @sql1;
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @sql1                                                                                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| UPDATE example SET id=ROUND(id*RAND()),id2=ROUND(id2*RAND()),id3=ROUND(id3*RAND()),id4=ROUND(id4*RAND()),id5=ROUND(id5*RAND()),id6=ROUND(id6*RAND()) |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)