How can I replace a variable with the defined value of this variable in MySQL?

Question

Assign a string to a defined variable isWeekdays,

set @isWeekdays='calendar.monday=1 AND calendar.tuesday=1 AND calendar.wednesday=1 AND calendar.thursday=1 AND calendar.friday=1';

I expect that the variable isWeekdays can be replaced with the string while executing a query, for instance,

SELECT * FROM calendar WHERE @isWeekdays;

-- expect to    
SELECT * FROM calendar WHERE calendar.monday=1 AND calendar.tuesday=1 AND calendar.wednesday=1 AND calendar.thursday=1 AND calendar.friday=1;

However, it does not take effect.

Have a look at dynamic queries in MySql.i.e. stackoverflow.com/questions/23178816/… — artm
– artm, Commented Jun 17, 2015 at 20:52
mysql treats variable contents as opaque blobs. what you're doing is executed as the equivalent of select ... where 'solidstring'. if you want the variable's contents to be evaluated/executed as part of the query, you have to use dynamic sql: build a new query string, add the variable's contents, execute the new string. — Marc B
– Marc B, Commented Jun 17, 2015 at 21:04
seriously though, why even do this? are you just bored? need a hobby? — Tech Savant
– Tech Savant, Commented Jun 17, 2015 at 21:06
@NotoriousPet0 because I use @isWeekdays many times in my sql queries. Now, I know dynamic queries can make it. — SparkAndShine
– SparkAndShine, Commented Jun 17, 2015 at 21:58
Why not just make it a variable of whatever scripting language your using? — Tech Savant
– Tech Savant, Commented Jun 17, 2015 at 21:59

Ivan Cachicatari · Accepted Answer · 2015-06-17 21:02:43Z

2

@isWeekdays will evaluate as numeric in query, you must use a statement and it will work as dynamic query:

-- setting the @isWeekdays variable
set @isWeekdays='calendar.monday=1 AND calendar.tuesday=1 
     AND calendar.wednesday=1 AND calendar.thursday=1 
      AND calendar.friday=1';


-- using the @isWeekdays variable in dynamic query
set @sql = concat('SELECT * FROM calendar WHERE ',@isWeekdays);
prepare stmt1 FROM @sql; 
execute stmt1; 
-- you muyst close the prepared statement
deallocate prepare stmt1;

You can change @isWeekdays in the same session and open the statement again.

That's it.

answered Jun 17, 2015 at 21:02

Ivan Cachicatari

4,3143 gold badges24 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SparkAndShine · Accepted Answer · 2015-07-08 21:23:05Z

With the help of @Notorious Pet0, compared with dynamic queries, using script language seems better. The key source codes (using format strings in python) is as follows.

#!/usr/bin/env python
import MySQLdb
import codecs  #for charset utf-8

## connect to MySQL
db = MySQLdb.connect(host="localhost",
        user="root",
        passwd="Jelline", # password
        db="gtfs")  #name of the data base

## create a Cursor object to execute sql queries
cur = db.cursor()

## execute queries
isWeekdays = 'calendar.monday=1 AND calendar.tuesday=1 AND calendar.wednesday=1 AND calendar.thursday=1 AND calendar.friday=1'
sql = 'SELECT * FROM calendar WHERE {isWeekdays}'.format(isWeekdays=isWeekdays)

cur.execute(sql)

## write to a file
with open('test.txt', 'w') as fp : #fp is a file object
    for row in cur.fetchall() :
        s = '\t'.join(str(item) for item in row)
        fp.write(s + '\n')

## clean up
fp.close()
cur.close()
db.close()

Collectives™ on Stack Overflow

How can I replace a variable with the defined value of this variable in MySQL?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related