backtick is not working to run mysql queries in sh

Question

Hi I am trying to run MySQL queries from shell script.

mysql -u root -p'1234' -e "CREATE TABLE $DB.aa_vv_cc
(
id int(10) unsigned NOT NULL AUTO_INCREMENT,
city varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
lat varchar(255) DEFAULT NULL,
`long` varchar(255) DEFAULT NULL,
 status int(11) NOT NULL DEFAULT '1',
 created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY ('id')
);"

I am getting error on "long".The back tick is not working from shell script.Any help will be appreciated.

Answer 1

Backticks are Command Substitution in the shell.

And they are evaluated in double quoted strings.

So the shell is seeing the

`long`

in your string and trying to run the command long and, presumably, failing.

You need to escape the backticks

\`long\`

in the double quoted string to prevent that (or use a single quoted string which doesn't evaluate them)

'... `long` ...'

Answer 2

You have to try this here i have modify two things 'long' replace by \'long\' and PRIMARY KEY ('id') replace by PRIMARY KEY (id) this query is work in my shell script.Here the test is the name of database.

mysql -u root  -p'1234' -e "USE test;CREATE TABLE $DB.aa_vv_cc (id int(10) unsigned NOT NULL AUTO_INCREMENT,city varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,lat varchar(255) DEFAULT NULL,\`long\`varchar(255) DEFAULT NULL, status int(11) NOT NULL DEFAULT '1', created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp NOT NULL DEFAULT '0000-00-0000:00:00',PRIMARY KEY (id));"