A minor feature of CHAR and VARCHAR

Prehistory

There is a small server on which standard LAMP is spinning. It all started with the fact that QA approaches me and says: “There is a topic, I need to double-check the registration of users, can you delete the old account?”, “No question” - I answered. The point is, the entrance is made only through social networks. In order not to violate the integrity of the database by deleting the account, I decided to just take and rename the UID (user ID in a specific social network) in the table.
Since everyone's UID is different (vk, facebook, google ... - numeric UID, linkedin - string UID) was used by VARCHAR for storage. As a result, I added the underscore character `_` to the line, and with a calm soul unsubscribed:" Check ... ".


')


I was very surprised when I heard: “Did you just delete the account, otherwise my old one is displayed?”.
In the course of the mini investigation, a discrepancy was found.

/** * @param string $providerUserId * @param string|null $provider * * @return ent\UserSocial|null */ public function getByProviderUserId($providerUserId, $provider = null) { $where = 'providerUserId = ?'; if ($provider) { $where .= ' AND provider = "' . $provider . '"'; } $res = $this->fetchObjects($where, [$providerUserId]); if (empty($res)) { return null; } return $res[0]; } 

Namely:

$ where = 'providerUserId =?';

It turned out the addition of `_` - had no effect on the sample, since the UID was a number.
During the experiments, the following data were obtained:



Test number 1:

mysql> select * from t where `string` = 123456;
+----+----------+
| id | string |
+----+----------+
| 1 | 123456 |
| 2 | 123456_ |
| 3 | 123456a |
| 5 | 123456_a |
+----+----------+
4 rows in set, 2 warnings (0.00 sec)


Test number 2:

mysql> select * from t where `string` = '123456';
+----+--------+
| id | string |
+----+--------+
| 1 | 123456 |
+----+--------+
1 row in set (0.00 sec)


You need to check the usual CHAR (well, add the index, you never know ...)
Test number 3:

mysql> select * from t2 where `string` = 123456;
+----+----------+
| id | string |
+----+----------+
| 1 | 123456 |
| 3 | 123456a |
| 4 | 123456_ |
| 5 | 123456_a |
+----+----------+
4 rows in set, 3 warnings (0.00 sec)


Test number 4:

mysql> select * from t2 where `string` = '123456';
+----+--------+
| id | string |
+----+--------+
| 1 | 123456 |
+----+--------+
1 row in set (0.00 sec)


Going to the official on the page , I did not find anything similar. As a result, I had to tweak the request so that the UID would be perceived as a string, and not as a number.

Ps. Now when I need to delete something, I add `_` ahead :)
PPS: Link by ellrion to the description of this chip.