A script to find int columns in a MySQL database that are nearing the maximum value for the given size. This supports all types of int columns, and also supports unsigned columns.This is especially useful for auto incrementing primary keys and their related foreign keys.

column-max-check.php

<?php
$config = (object) array(
    'db' => (object) array(
        'host' => '127.0.0.1',
        'username' => 'root',
        'password' => '',
    ),
    // Threshold for how "full" the int columns are, as a percentage. Since we
    // deal with columns of different sizes, a fixed number isn't appropriate
    'threshold' => 0.95,

    // Columns to ignore from checking, in schema.table.column format
    'ignoredColumns' => array(
    ),
);

$db = new PDO(
    sprintf('mysql:host=%s', $config->db->host),
    $config->db->username,
    $config->db->password,
    array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    )
);

// Ignore the built-in MySQL tables; we don't worry about them
$query = 'SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE DATA_TYPE LIKE \'%int\'
AND TABLE_SCHEMA NOT IN (\'information_schema\', \'mysql\')';

$statement = $db->prepare($query);
$statement->setFetchMode(PDO::FETCH_OBJ);
$statement->execute();

// @see http://dev.mysql.com/doc/refman/5.6/en/integer-types.html
$maxValues = array(
    'tinyint' => 127,
    'smallint' => 32767,
    'mediumint' => 8388607,
    'int' => 2147483647,
    'bigint' => 9223372036854775807,
);

foreach ($statement as $row) {
    $column = sprintf(
        '%s.%s.%s',
        $row->TABLE_SCHEMA,
        $row->TABLE_NAME,
        $row->COLUMN_NAME
    );

    if (in_array($column, $config->ignoredColumns)) {
        continue;
    }

    $query = sprintf(
        'SELECT MAX(`%s`) FROM `%s`.`%s`',
        $row->COLUMN_NAME,
        $row->TABLE_SCHEMA,
        $row->TABLE_NAME
    );

    $maxStatement = $db->query($query);

    $maxStatement->execute();
    $value = $maxStatement->fetchColumn();
    $max = $maxValues[$row->DATA_TYPE];

    // Unsigned columns are twice the maximum of signed columns + 1
    if (false !== strrpos($row->COLUMN_TYPE, 'unsigned')) {
        $max = $max * 2 + 1;
    }

    if ($value / $max >= $config->threshold) {
        echo 'Column: ', $column, PHP_EOL;
        echo 'Max allowed: ', $max, PHP_EOL;
        echo 'Current value: ', $value, PHP_EOL;
        echo 'Remaining: ', $max - $value, PHP_EOL;
        echo PHP_EOL;
    }
}