Why MySQL DELETE Fails Without a Primary Key and How ESCAPE Fixes It

When attempting to delete rows from a MySQL table without a primary key, a simple

DELETE FROM a WHERE device_id = '1239898' AND task_id = '00111133445566';

statement may not remove any data.

Using Navicat’s graphical interface, the same deletion succeeds because the tool generates a slightly different statement that includes a LIKE comparison and an ESCAPE '#' clause:

DELETE FROM a WHERE device_id = '1239898' AND task_id LIKE '00111133445566' ESCAPE '#';

The ESCAPE keyword defines an alternate escape character for LIKE patterns, allowing characters such as % to be treated literally. For example, to match the literal string 陈% you would write LIKE '陈#%%' ESCAPE '#', where # acts as the escape character.

In the discussed case, the presence of ESCAPE '#' was a side effect of the table lacking a primary key; Navicat adds this clause to ensure correct pattern matching when generating the delete statement.

Further investigation revealed that the original query had the device_id and task_id values swapped, so the WHERE conditions were incorrect. The correct query should target the proper columns, and with a proper primary key the generated SQL would simply use WHERE id = … without any ESCAPE clause.

The article concludes that the odd ESCAPE '#' addition was not a MySQL bug but a result of a missing primary key and a mistaken condition order, illustrating how small oversights can lead to confusing debugging sessions.