Automatically clip strings to the proper length on insert

2
votes

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: 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.

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.

3
phpmysqlpdo
Shouldn't you know the length of your fields before you insert into them...as in you know what fields you're planning on inserting into? - Ian Elliott
To keep DRY you could read the length of the database field and use it in PHP to cut your input string. But I think your application (cour PHP code) must know what kind of data you want to save. This means you should configure this in PHP. - powtac
the query fails? In my experience most MySQL servers automatically truncate fields to the length of the varchar without getting upset at all. - Matt Brunmeier
I do know what fields I insert. but then if I decide to make the database fields longer, I have to change the lengths in two places. seems rather silly. - Jason S

3 Answers

9
votes

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

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

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.

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

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

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.

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

0
votes

You can use column metadata to check string length. (PHP Manual on PDOStatement->getColumnMeta)

Get metadata for whole table this way

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

you could query information_schema to get the lenght of the column and use that data to truncate before insert, but that's more overhead than necessary. just turn off strict SQL mode for this statement:

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