1

I have the following snippet 

select user_id into @selected_user from users where mail = 'mymail@gmail.com';
select * from kc_network_user where user_id = 'previous_id';

which I want to modify to be parametric. I found the following resources:

  1. Using variables in SQL script
  2. How to declare a variable in MySQL?
  3. Declare variables - MySQL Workbench gives me an error with DECLARE

That's what I did:

 select @user_mail := 'mymail@gmail.com'; #1
select @user_mail; #2
select user_id into @selected_user from user where mail = @user_mail; #3
select @selected_user;
select * from user_network where user_id = @selected_user; #4

In select #1 I tried also the following:

set @user_mail = 'mymail@gmail.com';

The select #2 returns the correct value, so the variable is initialized.
The select #3 returns the following error:

Error Code: 1267. Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

While the select #4 runs correctly (if I set the mail parameter with the literal value).

What's the difference between those queries? How can I set a variable before doing a query?

A. Wolf
  • 1,309
  • 17
  • 39
  • 1
    'MySQL Workbench gives me an error with DECLARE' - declare variables can only be used in stored programs (procedure,functions,triggers,events). – P.Salmon Jan 14 '20 at 12:06
  • *The select #3 returns the following error* `mail` collation is defined by database, table or field settings. `@user_mail` collation is defined by your connection settings (afair). They're different and are not coercible, so error is generated. – Akina Jan 14 '20 at 12:12
  • But you can set the session collation - see the answer from grid vost here https://stackoverflow.com/questions/28540772/using-utf8mb4-in-mysql. BTW is there a reason why you are doing this when it could be done in 1 query. – P.Salmon Jan 14 '20 at 12:15
  • Does this answer your question? [Troubleshooting "Illegal mix of collations" error in mysql](https://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql) – P.Salmon Jan 14 '20 at 12:26
  • Thank you, I think that's the reason why I get that error. I don't understand how to apply it in my specific case. – A. Wolf Jan 15 '20 at 19:25

1 Answers1

0

I don't understand why not simply

select * 
from user_network
join user using (user_id)
where user.mail = 'mymail@gmail.com';

If you need to set mentioned variables additionally (but for what reason?) then use

select *, (@selected_user := user.user_id) 
from user_network
join user using (user_id)
where user.mail = (@user_mail := 'mymail@gmail.com');
Akina
  • 39,301
  • 5
  • 14
  • 25
  • Because I have to deal with many tables and different queries. It would be difficult to manage all these queries one by one, while it would be easier manage only some "global" variables. – A. Wolf Jan 14 '20 at 13:47