Home > database >  Why do my MySQL variables show up a null?
Why do my MySQL variables show up a null?

Time:11-26

I am trying to understand MySQL functions. First, I would like to set a variable, then display it. I'm doing this in phpMyAdmin on my laptop. I am using php 7.4. I don't know if this is a permissions thing. I have super privileges.

All the info I read says:

SET @myvar1 = 100;
SELECT @myvar1;

Sure enough, I see 1 column @myvar1 with the value 100

Now if I just enter:

SELECT @myvar1;

I only I see 1 column @myvar1 with the value null.

Is such a variable a 1-off thing, gone after I read it once??

The MySQL docs show this for user variables (X is for hexadecimal, I presume):

SET @v1 = X'41';
SET @v2 = X'41' 0;
SET @v3 = CAST(X'41' AS UNSIGNED);
SELECT @v1, @v2, @v3;

Sure enough, I see @var2 and @var3 as 65, but @var1 shows as [BLOB - 1 B]

If I repeat the SELECT query, all 3 show as null. I thought user variables should last for the entire user session. Is that wrong?

If I run the SET queries first, then the SELECT query, they all show as null

CodePudding user response:

Like @Zaffy said, phpmyadmin is messing with MySql connections behind your back. It's a tolerable tool for doing things like creating tables.

It's a terrible tool for learning how to use SQL. To use SQL effectively, we need to understand things that exist in connection scope. @variables are connection scoped. So are temporary tables, insert id values, and other things. phpmyadmin opens lots of new connections to do what you tell it, so the connection-scoped things you must learn to use don't work right. Look at the mysql command line tool or HeidiSQL on Windows. There are many other nice SQL client programs.

  • Related