用PHP编程显示违反MySQL唯一约束的列名 - Programmatically display column names violating MySQL unique constraint with PHP

Programmatically display column names violating MySQL unique constraint with PHP

MySQL返回23000错误代码时，表示更新/插入查询触发了完整性约束冲突。然而，错误，例如

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '22-offline-mpu-l' for key 'client_ad'

然而，如何判断哪些列需要唯一的组合？

上表模式为：

CREATE TABLE `ad` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `client_id` smallint(5) unsigned NOT NULL,
  `slug` varchar(100) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `status` tinyint(3) unsigned NOT NULL,
  `width` smallint(5) unsigned NOT NULL,
  `height` smallint(5) unsigned NOT NULL,
  `code` varchar(2040) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `client_ad` (`client_id`,`slug`),
  CONSTRAINT `ad_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `client` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1746 DEFAULT CHARSET=utf8;

这是一个建议，而不是一个最终的解决方案：

try {
    // Query
} catch ('PDOException $e) {
    if ($e->getCode() === '23000') {
        // SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '22-offline-mpu-l' for key 'client_ad'
        preg_match('/(?<='')[^'']*(?=''[^'']*$)/', $e->getMessage(), $match);
        $columns = $this->db->prepare("
        SELECT
            `f1`.`name`
        FROM
            `information_schema`.`innodb_sys_tables` `t1`
        INNER JOIN
            `information_schema`.`innodb_sys_indexes` `i1` USING (`table_id`)
        INNER JOIN
            `information_schema`.`innodb_sys_fields` `f1` USING (`index_id`)
        WHERE
            `t1`.`schema` = DATABASE() AND
            `t1`.`name` = :table_name AND
            `i1`.`name` = :key_name AND
            `i1`.`type` IN (2, 3)
        ORDER BY
            `f1`.`pos`;
        ")
            ->execute(['table_name' => static::$table_name, 'key_name' => $match[0]])
            ->fetchAll('PDO::FETCH_COLUMN);
        // $columns contains names of the columns that make up the index with the integrity constraint.
    } else {
        throw $e;
    }
}

这使用正则表达式来匹配关键字名称，然后查找具有数据库、表和关键字名称组合的关联列。innodb_sys_indexes.type 2和3是唯一的(http://dev.mysql.com/doc/refman/5.6/en/innodb-sys-indexes-table.html)（应用于表的）键需要整个表的唯一值。

看起来问题出在引用client表的外键上，所以我会这样做：

SHOW INDEXES FROM client WHERE Non_unique = 0;

看看你得到的专栏。

http://dev.mysql.com/doc/refman/5.0/en/show-index.html