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

Question

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

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

编辑2: Ugh。这是错误的方法；即使我让它在插入时正常工作，如果我想检查重复的字符串，它也不会正确匹配。

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 | 
+------------+

如果设置了strict SQL模式，它会将警告转换为错误并拒绝插入。

请回复您的评论: MySQL模式是一种Server配置。这可能不是PDO导致的，但从另一方面来说，这是可能的，因为任何客户端都可以为其会话设置SQL模式。

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

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

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

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

Answer 2

您可以查询information_schema以获得列的长度，并在insert之前使用该数据进行截断，但这超出了必要的开销。只需关闭此语句的严格SQL模式：

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

Answer 3

您可以使用列元数据检查字符串长度。(PHP Manual on PDOStatement->getColumnMeta)

通过这种方式获取整个表的元数据

$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;
}