How do I correctly compare a MySQL text field to a string of arbitrary length?

Question

Say I have a table that uses a string as its Primary Key. For example, a sessions table ( I will keep this to 4 characters so I can make my examples clearer).

I will also add an Auto Increment column to keep my example closer to reality (in case it makes a difference).

CREATE TABLE sessions (
  `id` INT AUTO INCREMENT,
  `key` CHAR(4) NOT NULL,
  PRIMARY KEY(`key`),
  UNIQUE KEY `idx_key` ( `key` )
);

I can insert data into the table.

INSERT INTO sessions (`key`) VALUES ('abcd');

Because this is a session ID it comes from a browser, so I don't trust it and use proper bind values in the code that handles the requests. A malicious user can send various strings here but they will always end up as strings and not injection attacks. That's fine. MySQL will happily truncate this data and issue a warning.

INSERT INTO sessions(`key`) VALUES ('abcdefg');
Warning (Code 1265): Data truncated for column 'key' at row 1

However, this is only "fine" (it's not fine, but whatever) for creating rows. The same operation first looks for a row before trying to insert it. You know - upsert. MySQL does not bother to truncate this data, and decides it is not in the table.

SELECT * FROM sessions WHERE `key` = 'abcdefg';
Empty set (0.00 sec)

This means that my insert from earlier was even more not fine, because when I insert the data, MySQL truncates it to a value that does exist.

INSERT INTO sessions (`key`) VALUES ('abcdefg');
Warning (Code 1265): Data truncated for column 'key' at row 1
Error (Code 1062): Duplicate entry 'abcd' for key 'idx_key'

The website code does not know the constraint on this column and I have no intention of giving it this information.

How can I get MySQL to compare only the first N characters of the string, where N is the (max) length of the text field?

I updated your key exampls to be letters rather than numbers, as per your actual question. — Martin, Aug 15 '19 at 12:02
@Barmar - The only indexing requirement for `AUTO_INCREMENT` is that it be the first column in _some_ index. It does not have to be the PK. — Rick James, Aug 15 '19 at 16:52

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

There are various MySQL functions for doing this in a round-about way.

The easiest to use is LEFT Manual, Q&A

SELECT COUNT(*) as numb FROM sessions WHERE `key` = LEFT(:stringVar,4)

Where :stringVar is a PDO Prepared Statement placeholder (and so completely safe in MySQL) for the given session value.

Therefore

if :stringVar = 'abcdefg' then the SQL above will check key = 'abcd' and ignore the rest of the string.

For flexibility, you can also use RIGHT if you so wish (in this case that would return 'defg').

WARNING:

With MySQL 5.7 and above, inserting data longer than the field length (as referenced in your question), by default will now abort the Insert / Update and return an error. Be aware of this and look this up.

So; to avoid this, apply the LEFT fnctionality upon insert as well:

INSERT INTO sessions (`key`) VALUES ( LEFT('abcdefg',4));

(untested)

If your key column length(s) are variable; you can dig them out dynamically, using MySQL INFORMATION_SCHEMA:

SELECT `CHARACTER_MAXIMUM_LENGTH` as ColumnLength FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `COLUMNS`.`DATA_TYPE` = 'char' AND `COLUMNS`.`TABLE_SCHEMA` = '<db_name>' AND `COLUMNS`.`TABLE_NAME` = '<table_name>'

In full:

INSERT INTO sessions (`key`) VALUES ( 
    LEFT(:stringVar, 
        (SELECT `CHARACTER_MAXIMUM_LENGTH`  
         FROM `INFORMATION_SCHEMA`.`COLUMNS`
         WHERE `COLUMNS`.`DATA_TYPE` = 'char' AND `COLUMNS`.`TABLE_SCHEMA` = '<db_name>' AND `COLUMNS`.`TABLE_NAME` = '<table_name>') ));

score 1 · Answer 2 · answered Aug 15 '19 at 12:21

1

You can use the SUBSTRING method.

Given N is the (max) length of the text field, you can say

SELECT * FROM sessions WHERE `key` = SUBSTRING('abcdefg', 1, N)

if N = 4 then we have

SELECT * FROM sessions WHERE `key` = SUBSTRING('abcdefg', 1, 4)

answered Aug 15 '19 at 12:21

Austyns

712
2
13
21

1

Only MySQL knows the 4, which means I can't put it in the query directly... Could I create a stored procedure with the 4 in it, and thus it is version controlled alongside the CREATE TABLE and I can use a single variable? Should I? – Altreus Aug 15 '19 at 13:17
Yes @Altreus you should. that should work and also makes more sense for re-usability and consistency of anticipated length of characters for CREATE and SELECT queries. – Austyns Aug 15 '19 at 13:56

score 0 · Answer 3 · answered Aug 15 '19 at 17:05

By chopping to 4 characters, you are saying that the rest of the string is not used for testing for equality.

The website code does not know the constraint on this column and I have no intention of giving it this information.

Storing into CHAR(4) implies truncttion. If you truncate the string, you have lost information. So the test cannot be performed.

How can I get MySQL to compare only the first N characters of the string, where N is the (max) length of the text field?

LEFT(input_string, 4) will do that. That "4" can be buried in the API that lies between the user and the database. You should not allow the user to directly perform INSERTs.

Other notes...

Beware of CHAR -- it pads to the specified length with spaces. Then it ignores trailing spaces when comparing. Hence 'abc ' and 'abc' will compare equal, as will the truncation of 'abc def'.

INSERT .. ON DUPLICATE KEY .. is a single-statement way of either inserting or updating.

Maybe you would be interested in this: You could "digest" the text with some "hashing" function, such as MD5 or SHA256. The digest is a fixed length. It can be used for testing for equality. (There is an extremely low chance that two different texts would hash to the same value.)

How do I correctly compare a MySQL text field to a string of arbitrary length?

3 Answers3

WARNING: