MySQL how to find the longest identifier name?-CodePudding

How can I find the longest identifier name in a MySQL database? short of looping identifiers in a scripting language ala foreach("SHOW TABLES" as table){SHOW COLUMNS FROM TABLE}, i haven't found any SQL-way of finding it

CodePudding user response：

To simply answer the question

MySQL how to find the longest identifier name?

use information_schema.COLUMNS. Below query finds the longest identifier in all schemas, you can restriscted by adding the following condition AND TABLE_SCHEMA='your_database'

SELECT COLUMN_NAME,TABLE_SCHEMA,TABLE_NAME,LENGTH(COLUMN_NAME)
FROM information_schema.COLUMNS 
WHERE LENGTH(COLUMN_NAME) in (SELECT MAX(LENGTH(COLUMN_NAME)) as max_column_name FROM  information_schema.COLUMNS );

Tested:

mysql> SELECT COLUMN_NAME,TABLE_SCHEMA,TABLE_NAME,LENGTH(COLUMN_NAME)
    -> FROM information_schema.COLUMNS
    -> WHERE LENGTH(COLUMN_NAME) in (SELECT MAX(LENGTH(COLUMN_NAME)) as max_column_name FROM  information_schema.COLUMNS );
 --------------------------------------------------------- -------------------- -------------------------------------- --------------------- 
| COLUMN_NAME                                             | TABLE_SCHEMA       | TABLE_NAME                           | LENGTH(COLUMN_NAME) |
 --------------------------------------------------------- -------------------- -------------------------------------- --------------------- 
| LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP | performance_schema | replication_applier_status_by_worker |                  55 |
 --------------------------------------------------------- -------------------- -------------------------------------- --------------------- 
1 row in set (0.05 sec)

CodePudding user response：

I hope someone has a better solution, but here's a loop implementation in PHP, prints

$ php longest_identifier_names.php
string(31) "blocket_external_targetbookings"
string(32) "is_available_for_all_subscribers"
string(64) "blocket_external_targetbookings.is_available_for_all_subscribers"

code:

<?php

declare(strict_types=1);

/**
 * quote identifiers like column names, table names, aliases
 * warning: does not support identifiers with dots in the name, like
 * `ads.productid` will be interpreted as
 * `ads`.`productid` (table ads, column productid), not `ads.productid`
 *
 * @param string $identifier
 * @return string
 * @throws \LengthException if identifier is too long
 */
function quoteIdentifier(string $identifier): string
{
    // identifiers have different escaping rules than other strings
    // https://www.codetinkerer.com/2015/07/08/escaping-column-and-table-names-in-mysql-part2.html
    $ret = '`' . strtr($identifier, array(
        '`' => '``',
        '.' => '`.`'
    )) . '`';
    if(strlen($ret) > 66){
        // MySQL does weird/scary things when identifiers are too long,
        // protect against possible "SQL Truncation Vulnerability" attacks..
        throw new \LengthException('Identifier too long: ' . $identifier); 
    }
    return $ret;
}

$host = 'mysql.foo.com';
$db = 'dbname';
$user = 'username';
$pass = 'password';
$db = new \PDO("mysql:host={$host};dbname={$db};charset=utf8mb4", $user, $pass, array(
    \PDO::ATTR_EMULATE_PREPARES => false,
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
));

$longest_table = "";
// @var string[] $tables
$tables = [];
foreach($db->query("SHOW TABLES", PDO::FETCH_NUM) as $row) {
    $table = $row[0];
    if(strlen($table) > strlen($longest_table)) {
        $longest_table = $table;
    }
    $tables[] = $row[0];
}
$longest_field = "";
foreach($tables as $table) {
    foreach($db->query("SHOW COLUMNS FROM ". quoteIdentifier($table), PDO::FETCH_ASSOC) as $row) {
        $field = $row['Field'];
        if(strlen($field) > strlen($longest_field)) {
            $longest_field = $field;
        }
    }
}
var_dump($longest_table, $longest_field, "{$longest_table}.{$longest_field}");