在插入时自动将字符串剪切到适当的长度 [英] Automatically clip strings to the proper length on insert

问题描述

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

I have a table with various VARCHAR fields in MySQL. I would like to insert some user data from a form via PHP. Obviously if I know the field lengths in PHP, I can limit the data length there with substr(). But that sort of violates DRY (field length stored in MySQL and as a constant in my PHP script). Is there a way for me to configure an INSERT so it automatically chops off excessively-long strings, rather than fails?

edit:当我的字符串太长时，它在PHP/PDO中失败(或至少引起异常).不知道我必须在PHP/PDO中做什么，以便它做正确的事.

edit: it's failing (or at least causing an exception) in PHP/PDO, when I have excessively long strings. Not sure what I have to do in PHP/PDO so it Does The Right Thing.

嗯.这是错误的方法.即使我可以在INSERT上正常运行，但是如果我要检查重复的字符串，也无法正确匹配.

edit 2: Ugh. This is the wrong approach; even if I get it to work ok on INSERT, if I want to check for a duplicate string, it won't match properly.

推荐答案

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

Actually, MySQL truncates strings to the column width by default. It generates a warning, but allows the insert.

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模式，它将警告变成错误并拒绝插入.

If you set the strict SQL mode, it turns the warning into an error and rejects the insert.

您的评论:SQL模式是MySQL Server配置.可能不是PDO引起的，但是另一方面，这是可能的，因为任何客户端都可以为其会话设置SQL模式.

Re your comment: SQL mode is a MySQL Server configuration. It probably isn't PDO that's causing it, but on the other hand it's possible, because any client can set SQL mode for its session.

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

You can retrieve the current global or session sql_mode value with the following statements:

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

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

The default should be an empty string (no modes set). You can set SQL mode in your my.cnf file, with the --sql-mode option for mysqld, or using a SET statement.

更新:默认情况下，MySQL 5.7和更高版本设置严格模式.参见 https://dev.mysql.com/doc/refman /5.7/zh-CN/sql-mode.html

Update: MySQL 5.7 and later sets strict mode by default. See https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

这篇关于在插入时自动将字符串剪切到适当的长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！