MySQL检索数据类型== X的列名

I need to write some code that applies to only certain fields within our database. Specifically I'm looking for any field in all tables of a single database that are of type DECIMAL(12,5);

我需要编写一些仅适用于数据库中某些字段的代码。具体来说，我正在寻找DECIMAL类型的单个数据库的所有表中的任何字段（12,5）;

If it's of any help, all of our decimal fields are prefixed with dec_. I could gather all of the decimal fields from all tables, but there are tons of them that don't match my particular criteria, I need only the decimal values who's limit is 12,5.

如果它有任何帮助，我们所有的十进制字段都以dec_为前缀。我可以收集所有表中的所有十进制字段，但是有很多它们与我的特定条件不匹配，我只需要十进制值，其限制是12,5。

I'd have no preference between doing this in pure SQL, or using PHP. We're currently using the CakePHP framework to manage our project, though I could just as easily do this without using the framework. This is a one time operation to gather the required data so I can continue forward with the audit.

在纯SQL或使用PHP时，我没有偏好。我们目前正在使用CakePHP框架来管理我们的项目，尽管我可以在不使用框架的情况下轻松完成。这是一次收集所需数据的操作，因此我可以继续进行审核。

Any help is greatly appreciated, thank you for taking the time to read this.

非常感谢任何帮助，感谢您抽出宝贵时间阅读本文。

[EDIT] Got some great answers here, thanks everyone! I probably should have mentioned this in my initial post, but is there any way I can also get the table name associated with each field?

[编辑]在这里得到了一些很棒的答案，谢谢大家！我可能应该在我的初始帖子中提到这一点，但是有什么办法我可以得到与每个字段相关联的表名吗？

3 个解决方案

#1

SELECT
    TABLE_NAME,
    COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'db-name' AND
    DATA_TYPE = 'decimal' AND
    NUMERIC_PRECISION = 12 AND
    NUMERIC_SCALE = 5

#2

You can try to use the following approach:

您可以尝试使用以下方法：

$pdo = new PDO(...);

$sql = "select column_name from 
    information_schema.columns 
    where table_schema='$your_db' and table_name='?' and  data_type = 'decimal(12,5)'";

foreach($pdo->query("SHOW TABLES;") as $table) {
    $prepared = $pdo->prepare($sql);
    $prepared->execute(array($table));
    print_R($prepared->fetchAll()) ;  
}

#3

Try this

尝试这个

SELECT table_name, column_name
FROM information_schema.columns where data_type = 'INT'

#1

SELECT
    TABLE_NAME,
    COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'db-name' AND
    DATA_TYPE = 'decimal' AND
    NUMERIC_PRECISION = 12 AND
    NUMERIC_SCALE = 5

#2