There are two ways to set a variable from a query, and they behave differently, especially when no record is matched.
You can SELECT ... INTO the variable...
select first_name into @z from actor where actor_id = 999999;
Or you can SET it to the value returned by a scalar subquery.
set @z = (select first_name from actor where actor_id = 999999);
Illustrating the difference with the sample database...
mysql> use sakila;
Database changed
Our variable starts out NULL.
mysql> select @z;
+------+
| @z |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
mysql> SELECT first_name INTO @z FROM actor WHERE actor_id = 1;
Query OK, 1 row affected (0.00 sec)
We matched a row and our variable is set.
mysql> select @z;
+----------+
| @z |
+----------+
| PENELOPE |
+----------+
1 row in set (0.00 sec)
We try to select a record that doesn't exist:
mysql> select first_name into @z from actor where actor_id = 999999;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
So what's in our variable now?
mysql> select @z;
+----------+
| @z |
+----------+
| PENELOPE |
+----------+
1 row in set (0.00 sec)
I think this explains the confusion. When no row is found, the variable is unchanged from its previous value... so it's not that you have to "set it to null before you use it," it's that you have to reset it to null before you use it again, if you are doing something like SELECT ... INTO that won't reset the value if nothing is found.
On the other hand, if we use the other construct, the variable is reset when no record is matched.
mysql> select @z;
+----------+
| @z |
+----------+
| PENELOPE |
+----------+
1 row in set (0.00 sec)
mysql> set @z = (select first_name from actor where actor_id = 999999);
Query OK, 0 rows affected (0.00 sec)
mysql> select @z;
+------+
| @z |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
mysql>
User_tab_cols shows that there is a dependancy when virtual_column contains 'YES', which answers your need to determine if there is any dependency at all.
In order to determine what the actual virtual column consists of, one still needs to parse data_default.
SELECT column_name
, virtual_column
, segment_column_id
, internal_column_id
FROM user_tab_cols
WHERE table_name = 'T'
ORDER BY column_id;
Best Answer
Views and virtual columns are similar, in that neither are materialized. i.e. neither store any data or indexes, hence why your
index_length+data_lengthremain the same.Where they differ is:
As a side note, another commonly used feature with some overlap here is triggers. It is often better to have a virtual column than a trigger updating legacy columns on insert/update.
In response to this specific question:
There is no footprint, and you will likely not see overhead. Sure, there are hypothetical cases: If you have many virtual columns and
SELECT *from a client then more data will be sent across the network (since rows are sent in full; clients don't know they can reconstruct certain data from virtual column definitions).