在插入时自动将字符串修剪到适当的长度

Question

我在 MySQL 中有一个包含各种 VARCHAR 字段的表。 我想通过 PHP 从表单插入一些用户数据。 显然，如果我知道 PHP 中的字段长度，我可以使用 substr() 限制数据长度。 但这违反了 DRY（字段长度存储在 MySQL 中并作为我的 PHP 脚本中的常量）。 有没有办法让我配置 INSERT，以便它自动截断过长的字符串，而不是失败？

编辑：当我的字符串过长时，它在 PHP/PDO 中失败（或至少导致异常）。 不确定我必须在 PHP/PDO 中做什么，所以它做正确的事情。

编辑2：呃。 这是错误的做法； 即使我让它在 INSERT 上正常工作，如果我想检查重复的字符串，它也不会正确匹配。

Answer 1

实际上，MySQL 默认情况下会将字符串截断为列宽。 它会生成警告，但允许插入。

mysql> create table foo (str varchar(10));
mysql> insert into foo values ('abcdefghijklmnopqrstuvwxyz');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'str' at row 1 | 
+---------+------+------------------------------------------+
mysql> select * from foo;
+------------+
| str        |
+------------+
| abcdefghij | 
+------------+

如果设置严格的 SQL 模式，它将把警告变成错误并拒绝插入。

---

回复您的评论：SQL 模式是 MySQL 服务器配置。 可能不是 PDO 造成的，但另一方面这是可能的，因为任何客户端都可以为其会话设置 SQL 模式。

您可以使用以下语句检索当前全局或会话 sql_mode 值：

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

默认值应为空字符串（未设置模式）。 您可以在 my.cnf 文件中使用 mysqld 的 --sql-mode 选项或使用 SET 语句设置 SQL 模式。

更新：MySQL 5.7及更高版本默认设置严格模式。 请参阅 https://dev.mysql.com/doc/refman /5.7/en/sql-mode.html

Answer 2

您可以使用列元数据来检查字符串长度。 (PDOStatement->getColumnMetaPHP 手册)

获取整个表的元数据方式

$query = $conn->query("SELECT * FROM places");
$numcols = $query->columnCount();

$tablemeta = array();
for ($i = 0; $i < $numcols; $i++) {
    $colmeta = $query->getColumnMeta($i);
    $tablemeta[$colmeta['name']] = $colmeta;
}

Answer 3

您可以查询 information_schema 来获取列的长度，并使用该数据在插入之前进行截断，但这会带来不必要的开销。 只需关闭此语句的严格 SQL 模式即可：

SET @prev_mode = @@sql_mode;
SET sql_mode = '';
INSERT blah....;
SET sql_mode = @prev_mode;