-1

I am trying to get a better understanding of the context in which MySQL interprets wildcards, especially the _ underscore wildcard, which matches exactly one character. For example, it is a common practice to use snake case in naming databases and tables (e.g. my_sample_database and my_table) and in that context MYSQL does not treat the _ underscore character as a wildcard.


We create table named my_table and populate it as follows:

CREATE TABLE my_table
(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name CHAR(40) NOT NULL DEFAULT '',
  age INT NOT NULL DEFAULT 0,
  PRIMARY KEY(id)
);

INSERT INTO my_table(name, age) VALUES('William', 25);

We create table named my0table and populate it as follows:

CREATE TABLE my0table
(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name CHAR(40) NOT NULL DEFAULT '',
  age INT NOT NULL DEFAULT 0,
  PRIMARY KEY(id)
);

INSERT INTO my0table(name, age) VALUES('Bart', 15);

Sample database has two tables:

mysql> SHOW TABLES;
+------------------------------+
| Tables_in_my_sample_database |
+------------------------------+
| my0table                     |
| my_table                     |
+------------------------------+
2 rows in set (0.00 sec)

We execute the select statement and the DB gives unambiguous results:

mysql> SELECT * FROM my_table;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | William |  25 |
+----+---------+-----+
1 row in set (0.01 sec)

mysql> SELECT * FROM my0table;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Bart |  15 |
+----+------+-----+
1 row in set (0.00 sec)
5
  • unambiguous how`? – nbk Jan 12 at 18:12
  • @nbk my_table is not confused for my0table – Sandeep Jan 12 at 18:34
  • You insertWilliam 25 ijn my_table and your select shows that row and you insert Bart in my0table and the select shiws that result, so what is your problem exactly – nbk Jan 12 at 18:47
  • 1
    Object names are never expected to be wildcards. Wildcards are only expected in the context of pattern-matching operators, such as LIKE. – mustaccio Jan 12 at 19:02
  • It's really very simple - the _ (underscore character) is permitted as a character for a table name AND/OR a field_name - it's only when it appears as DATA, i.e. in a field that you have to escape it for searching - see here. – Vérace Jan 12 at 19:06
2

As you can see underscore is a wildcard in a LIKE comparison.

If you want the underscore recognizes you need to escape it with \ ´

But this is basic sql and nothing strange.

Finally it is good practise not to use wildcards reseved words in table names or column names, so you don't get confused and of course all others

SELECT table_name FROM information_schema.tables
WHERE table_name Like 'my_table'
| TABLE_NAME |
| :--------- |
| my_table   |
| my0table   |
SELECT table_name FROM information_schema.tables
WHERE table_name Like 'my\_table'
| TABLE_NAME |
| :--------- |
| my_table   |

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.