By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
version() |
---|
8.0.36 |
CREATE TABLE `user` (
`id` INT UNSIGNED AUTO_INCREMENT NOT NULL,
`name` VARCHAR(255),
PRIMARY KEY(`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;
insert into `user` (`id`, `name`) values
(1, 'john'),
(2, 'li\\ma'); -- value with one backslash
Records: 2 Duplicates: 0 Warnings: 0
select * from `user`
where `name` like 'li\\ma' escape '';
-- 1:1 search, no escape, expecting 1 row
id | name |
---|---|
2 | li\ma |
select * from `user`
where `name` like 'li%\\ma' escape '';
-- the same with % (matching 0 or more bytes), expecting 1 row
id | name |
---|---|
2 | li\ma |
select * from `user`
where `name` like 'li\\ma' escape '\\';
-- backslash escapes "m", expecting 0 rows
id | name |
---|
select * from `user`
where `name` like 'li%\\ma' escape '\\';
-- the same with %, expecting 1 row
id | name |
---|---|
2 | li\ma |
select * from `user`
where `name` like 'li\\\\ma' escape '\\';
-- backslash escapes "\", expecting 1 row
id | name |
---|---|
2 | li\ma |
select * from `user`
where `name` like 'li%\\\\ma' escape '\\';
-- the same with %, expecting 1 row
id | name |
---|---|
2 | li\ma |