mysqldump with --where clause is not working

Question

 mysqldump -t -u root -p  mytestdb mytable --where=datetime LIKE '2014-09%'

This is what I am doing and it returns:

mysqldump: Couldn't find table: "LIKE"

I am trying to return all the rows where the column datetime is like 2014-09 meaning "all September rows".

Solution 1

You may need to use quotes:

mysqldump -t -u root -p  mytestdb mytable --where="datetime LIKE '2014-09%'"

Solution 2

Selecting dates using LIKE is not a good idea. I saw this method in one project. This causes huge DBMS load and slow system operation as no index by this table column used.

If you need to select date range use between:

where datetime between '2014-09-01' and '2014-09-30 23:59:59'

Comments

[tadman]

The reason here is the shell breaks arguments on spaces by default, so it interprets that as three unrelated arguments. Quotes groups them all together.

[nodejsj]

thanks. had to use double quotes then single quote as such mysqldump -t -u root -p mytestdb mytable --where="datetime LIKE '2014-09%'"

[bhttoan]

Index is used as long as you do not use the wildcard first so '2019-09%' will use the index as it does not include the wildcard first but '%2014-09%' will not

[Timofey Bugaevsky]

The conversion to string might prevent using index. Some people also confirm that it is better not using like for date comparison: stackoverflow.com/questions/1629050/…

[Nikhil Wagh]

Can you also add the source of your answer?

[crazy_in_love]

@NikhilWagh what do you mean? I do not remember when I used a shell the first time, and quotes were one of the early gotchas I had to learn about, huh, I don't know, thirty years ago.

[Nikhil Wagh]

For reference: dev.mysql.com/doc/refman/8.0/en/mysqldump.html

[crazy_in_love]

Yeah, it says "[q]uotes around the condition are mandatory if it contains spaces or other characters that are special to your command interpreter". That's a feature of your shell.