1

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?

J.C Guzman
  • 1,192
  • 3
  • 16
  • 40
  • Are you running the queries in the same session ? How are you executing these queries ? Using PHPMyAdmin ? – Madhur Bhaiya Sep 26 '19 at 16:27
  • Yes I am running the queries in the same session, I am ussing MySQL workbench – J.C Guzman Sep 26 '19 at 16:28
  • What do you get if you execute `select @v1;`, to see if it still has the value of "name_to_filter"? – RToyo Sep 26 '19 at 16:47
  • I tossed it into an [SQL Fiddle](http://sqlfiddle.com/#!9/7dac36/1), and it seems to work fine. Do you have any additional SQL that might be interfering? – RToyo Sep 26 '19 at 17:04
  • No, the error is this: Error Code: 1267. Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) – J.C Guzman Sep 26 '19 at 17:09
  • @J.CGuzman That's a separate error about the charset of your data. Googling the error will get you some solutions. The fix is to either `alter` your tables so that they are the same collation, or `convert()` your values within your query. – RToyo Sep 26 '19 at 18:23
  • If I execute select @v1; it returns "name_to_filter", but when I put it in where clause I have the same error, why? – J.C Guzman Sep 26 '19 at 18:58
  • @J.CGuzman As [this answer](https://stackoverflow.com/a/49979633/4458445) on a question about that error points out, you can use `convert()` to change the collation. Try using `... where column1 = convert(@v1 using utf8mb4_unicode_ci)` (or using `utf8mb4_general_ci`). – RToyo Sep 26 '19 at 19:49

2 Answers2

1

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

J. Schmale
  • 446
  • 3
  • 6
0

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

Klienblat Moshe
  • 322
  • 1
  • 6
  • A session is essentially a connection, and is not terminated by a semicolon. Semicolons only separate statements, not sessions. – RToyo Sep 26 '19 at 16:44
  • This is not what I wanted, I need declare a variable outside of the select like this:https://stackoverflow.com/questions/34294844/parameterize-mysql-workbench-statements-how-to-define-variables/34295529#34295529 – J.C Guzman Sep 26 '19 at 16:57
  • The `SELECT @v1 = 'good'` will do a value comparison and return true or false (1 or 0), rather than set the value of the variable. If `@v1 = 'what ever'`, then `select @v1 = 'good'` will return a value of "0". The rest of this though, where you run `set @v1 = 'what ever'; select price1 from test.classification where type = @v1;` will work fine. – RToyo Sep 26 '19 at 17:07