CODEWITHSUNDEEP
mysql
J.C Guzman
J.C Guzman

Reputation: 1334

MySQL Variable assignment does not assign to variable - what am I doing wrong?

I am trying to assign a value to a a user-defined variable like this:

SET @v1='name_to_filter';


SELECT *
FROM mytable
WHERE column=@v1;

The problem is that this code returns 0 rows, but if instead of declare this variable I put the name to filter in the where close the code returns a few rows:

SELECT *
FROM mytable
WHERE column='name_to_filter';

So the variable is not saving the name I asigned to it, what am I doing wrong?

Upvotes: 1

Views: 948

Answers (2)

J. Schmale
J. Schmale

Reputation: 486

The collation of your user-defined variable defaults to your connection collation and that is different then the collation of the column you are trying to compare to. As a result mysql can't do the comparison correctly so it throws an error.

One solution is to change your connection collation to match you column collation. Run this statement before the rest:

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';

To confirm the collation of the column you could use this statement:

SHOW FULL COLUMNS FROM tablename

which returns a column named Collation

Upvotes: 1

Klienblat Moshe
Klienblat Moshe

Reputation: 347

SELECT @v1 = 'good',price1  FROM test.classification
where type = @v1;

when you put this code: 

set @v1 = 'what ever';

then the next query after that dose not recognize the @v1 because,

A user-defined variable is session specific i.e variable defined by one client is not shared to other client and when the session ends these variables are automatically expired. so when you put semi colon then the next query is another session

Upvotes: 0

Related Questions