MYSQL用户定义@VARIABLE

Question

I am coding a php-angularjs webapp and I am now creating a revision system with procedure and triggers on insert/update.

after the login I need to do:

$sql = 'SET @id_user := ' . (int)$_SESSION['user']['id_user'].';';

it works well if I select it (returns the user id as expected) but later on (1 min later) after a few queries and pages visited, when I update the customer table everything is good except the @user_id var which is null

Question: Is there a way I can set the var for my full session to avoid repeating the SET @var every time I need to log the modifications?

(it also needs to think that I have multiple admins)

Answer 1

User-defined variables are session-bound in MySQL. So, they exist in context of single connection.

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

If you will reuse same connection, then by definition, variables will be same (so, "shared"). But if your request initializes different connections, then you will not be able to access variables from one session in another.

As a conclusion - user-defined variables were never intended for state transition, it is unsafe and, actually, a side-effect to rely on them even across several queries in context of one session. They might be used to "tune" your query (let's say, enumeration-related issues as sequences are absent in MySQL), but I would not recommend to use them for state transfer.