Menu
I'm baffled as to what this SQL statement means:
SELECT exhibitor_categories+0 from exhibitor_registry
What is exhibitor_categories+0 ? It returns a number for each row returned.
exhibitor_categories is defined as:
set('contemporary', 'classical impression / transitional', 'outdoor', 'home accessories')
Thanks for your time :)
742
SELECT 0 FROM table does not return any column values of the table but rather a constant for every row of table - e.g. if you have the following table TABLE id | name | age 0 | John | 12 1 | Jack | 22 2 | Martin | 42.
For regular SQL, ISNULL(item) can only take one parameter, and thus 90% of these solutions don't work. This will return the value in column_name if it is not null , and 0 if it is null .
The SET statement that assigns a value to the variable returns a single value. When you initialize multiple variables, use a separate SET statement for each local variable. You can use variables only in expressions, not instead of object names or keywords. To construct dynamic Transact-SQL statements, use EXECUTE.
UPDATE [table] SET [column]=0 WHERE [column] IS NULL; Null Values can be replaced in SQL by using UPDATE, SET, and WHERE to search a column in a table for nulls and replace them. In the example above it replaces them with 0.
It implicity converts the set value to an INTEGER.
A set is treated as a bitmap, so the first value sets bit 0, the second value sets bit 1 etc.
mysql> CREATE TABLE exhibitor_registry(exhibitor_categories set('contemporary',
'classical impression / transitional', 'outdoor', 'home accessories') NOT NULL);
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT
-> INTO exhibitor_registry
-> VALUES ('contemporary,classical impression / transitional,outdoor');
Query OK, 1 row affected (0.03 sec)
mysql> SELECT exhibitor_categories
-> FROM exhibitor_registry;
+----------------------------------------------------------+
| exhibitor_categories |
+----------------------------------------------------------+
| contemporary,classical impression / transitional,outdoor |
+----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT exhibitor_categories + 0
-> FROM exhibitor_registry;
+--------------------------+
| exhibitor_categories + 0 |
+--------------------------+
| 7 |
+--------------------------+
1 row in set (0.00 sec)
Check out http://dev.mysql.com/doc/refman/5.0/en/set.html for the full skinny, but basically a field like that is known as an 'set' - it has a list of possible values, which can only be one or more of those. The value is stored as a number... which means the exhibitor_categories is actually storing the value 4 when someone sets the value to 'outdoor', because it's setting the third bit - '0100'. When you get the value back out of the database later, mysql automatically turns '0100' back into 'outdoor' for you.
But, by adding +0 to the query, you force the result to stay a number, so you would actually get the number value '0100' if the row's value was set to 'outdoor' in this case.
Apologies for getting enum and set mixed up.
Why, you might ask, is it setting the value to '0100' instead of just saying '3', like in an enum? Because a set can hold multiple values - if the values 'contemporary' (0001) and 'outdoor' (0100) were selected, it would store '0101' <- setting the 1st and 3rd bits, which would be returned as '5' if you use the +0 code.
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
