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".

score 70 · Accepted Answer · answered Oct 08 '14 at 16:21

70

You may need to use quotes:

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

answered Oct 08 '14 at 16:21

Leonardo Herrera

8,388
5
36
66

3

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. – tadman Oct 08 '14 at 16:23
1

thanks. had to use double quotes then single quote as such mysqldump -t -u root -p mytestdb mytable --where="datetime LIKE '2014-09%'" – nodejsj Oct 08 '14 at 16:23
Can you also add the source of your answer? – Nikhil Wagh Oct 22 '19 at 09:47
@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. – Leonardo Herrera Oct 23 '19 at 00:55
For reference: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html – Nikhil Wagh Oct 23 '19 at 07:24
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. – Leonardo Herrera Oct 23 '19 at 13:32

score 5 · Answer 2 · edited Jun 09 '19 at 19:14

5

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'

edited Jun 09 '19 at 19:14

Tom Zych

13,329
9
36
53

answered Jun 09 '19 at 18:51

Timofey Bugaevsky

126
2
3

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 – bhttoan Jun 09 '19 at 19:08
1

The conversion to string might prevent using index. Some people also confirm that it is better not using like for date comparison: https://stackoverflow.com/questions/1629050/sql-server-datetime-like-select – Timofey Bugaevsky Jun 11 '19 at 05:56

score 1 · Answer 3 · answered Apr 26 '22 at 11:17

Not the answer but just a notice, when using mysqldump it will automatically add DROP TABLE and CREATE TABLE to the export file, in case you don't want that add --skip-add-drop-table and --no-create-info with the command, like:

mysqldump -u root-p database_name table_name --skip-add-drop-table --no-create-info > export.sql

score 0 · Answer 4 · answered Jul 24 '22 at 08:45

You missed "" for where clause . datetime column name datetime is not recommended. It is a data type as well.

mysqldump -u root -p mytestdb mytable --where="datetime LIKE '2014-09%'
" > mytable.sql;

After executing the command a prompt will ask for MySQL password. then check your current folder for generated mystable.sql

mysqldump with --where clause is not working

4 Answers4

Linked