MySQL set variable hack

Consider the query:

SELECT @xxx As thevar FROM (SELECT @xxx:=1) as temp

Will the resulting query always yield 1, even if开发者_开发百科 there are many other similar queries running at the same time (or as close to the same time as they can get) that are setting @xxx to a different integer.

Also, what exactly is the scope of @xxx?

I'm trying to determine if this is a safe (albeit hacky) way to insure a variable is set to the value I want for the life a specific query.

These queries are run against a single instance of MySQL 5.5 and may be done on the same connection or different connections to the database.

From what I have just tested, it seems the variable is seen only on the current connection. Or more precisely on the connection on which it is set.

I believe your SQL Statement has the same effect as the SET statement for User Defined Variables.

The documentation for which states that:

User-defined variables are connection-specific. That is, a user variable defined by one client cannot be seen or used by other clients. All variables for a given client connection are automatically freed when that client exits.

Thus if the other queries are using there own separate connections, than they will have their own copies of @xxx, and will not interfere with each other. And your hack will work as expected. But if same connection is used and the order of query execution cannot be guaranteed then, other queries can change @xxx and affect the subsequent queries.

Regarding if this always return 1:

SELECT @xxx As thevar FROM (SELECT @xxx:=1) as temp

It should, BUT if using the same connection there is an outlier chance that a Seperate Thread using the same connection executes SET @xxx=2 just after the execution of the subquery and before outer select. Please correct me if I'm wrong about ATOMICITY of SELECT here.