MySQL Tutorial/Data Types/Set — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 12:53, 26 мая 2010
Содержание
- 1 If you set a SET column to an unsupported value, the value is ignored and a warning is issued
- 2 Looking for an exact match in a set column
- 3 Looking for values containing the first set member
- 4 MySQL stores SET and ENUM as numbers
- 5 Search for SET values using the FIND_IN_SET() function
- 6 Search for SET values using the LIKE operator
- 7 SET column type
- 8 SET Type
- 9 Using the SET type
If you set a SET column to an unsupported value, the value is ignored and a warning is issued
<source lang="sql">
mysql> mysql> CREATE TABLE myset (col SET("a", "b", "c", "d")); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO myset (col) VALUES ("a,d"),
-> ("d,a"), -> ("a,d,a"), -> ("a,d,d"), -> ("d,a,d");
Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT col FROM myset; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.00 sec) mysql> mysql> INSERT INTO myset (col) VALUES ("a,d,d,s"); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> mysql> SHOW WARNINGS; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1265 | Data truncated for column "col" at row 1 | +---------+------+------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT col FROM myset; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 6 rows in set (0.00 sec) mysql> mysql> mysql> drop table myset; Query OK, 0 rows affected (0.00 sec) mysql></source>
Looking for an exact match in a set column
Comparing set values to "val1,val2" returns different results than comparing values to "val2,val1".
<source lang="sql">
mysql> mysql> mysql> SELECT * FROM tbl_name WHERE set_col = "val1,val2"; mysql> mysql> mysql> CREATE TABLE myset (col SET("a", "b", "c", "d")); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO myset (col) VALUES ("a,d"),
-> ("d,a"), -> ("a,d,a"), -> ("a,d,d"), -> ("d,a,d");
Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT col FROM myset; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.00 sec) mysql> mysql> SELECT * FROM myset WHERE col = "a,d"; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.00 sec) mysql> mysql> drop table myset; Query OK, 0 rows affected (0.00 sec)</source>
Looking for values containing the first set member
<source lang="sql">
mysql> mysql> CREATE TABLE myset (col SET("a", "b", "c", "d")); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO myset (col) VALUES ("a,d"),
-> ("d,a"), -> ("a,d,a"), -> ("a,d,d"), -> ("d,a,d");
Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT col FROM myset; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.00 sec) mysql> mysql> SELECT * FROM myset WHERE col & 1; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.00 sec) mysql> mysql> drop table myset; Query OK, 0 rows affected (0.00 sec)</source>
MySQL stores SET and ENUM as numbers
They can be manipulated using numeric operations.
<source lang="sql">
mysql> mysql> CREATE Table Test(
-> Advertiser SET("A","B","C") -> );
Query OK, 0 rows affected (0.03 sec) mysql> mysql> mysql> INSERT INTO Test (Advertiser) values("A, B"); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> mysql> mysql> SELECT * FROM Test WHERE Advertiser = 1; +------------+ | Advertiser | +------------+ | A | +------------+ 1 row in set (0.02 sec) mysql> mysql> SELECT Advertiser, Advertiser + 0 FROM Test; +------------+----------------+ | Advertiser | Advertiser + 0 | +------------+----------------+ | A | 1 | +------------+----------------+ 1 row in set (0.00 sec) mysql> mysql> drop table Test; Query OK, 0 rows affected (0.00 sec) mysql></source>
Search for SET values using the FIND_IN_SET() function
<source lang="sql">
SELECT * FROM tbl_name WHERE FIND_IN_SET("value",set_col)>0;</source>
Search for SET values using the LIKE operator
SELECT * FROM tbl_name WHERE set_col LIKE "%value%";
<source lang="sql">
mysql> mysql> mysql> CREATE TABLE myset (col SET("a", "b", "c", "d")); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO myset (col) VALUES ("a,d"),
-> ("d,a"), -> ("a,d,a"), -> ("a,d,d"), -> ("d,a,d");
Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT col FROM myset; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.00 sec) mysql> mysql> select * from myset where col like "%a%"; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.00 sec) mysql> mysql> drop table myset; Query OK, 0 rows affected (0.01 sec) mysql></source>
SET column type
<source lang="sql">
mysql> mysql> CREATE TABLE myTable
-> ( -> ID SMALLINT UNSIGNED, -> Model VARCHAR(40), -> Color ENUM("red", "blue", "green", "yellow"), -> Options SET("rack", "light", "helmet", "lock") -> );
Query OK, 0 rows affected (0.03 sec) mysql> mysql> desc myTable; +---------+-------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------------------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | YES | | NULL | | | Model | varchar(40) | YES | | NULL | | | Color | enum("red","blue","green","yellow") | YES | | NULL | | | Options | set("rack","light","helmet","lock") | YES | | NULL | | +---------+-------------------------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> mysql> drop table myTable; Query OK, 0 rows affected (0.02 sec)</source>
SET Type
The SET type stores a list of values.
A SET is a string object that can have zero or more values.
In a SET type, you can choose more than one option to store.
A SET type can contain up to 64 items.
Each of which must be chosen from a list of values.
Multiple set members are separated by commas (",").
SET member values should not contain commas.
<source lang="sql">
mysql> mysql> mysql> CREATE Table Test(
-> Advertiser SET("A","B","C") -> );
Query OK, 0 rows affected (0.03 sec) mysql> mysql> mysql> INSERT INTO Test (Advertiser) values("A, B"); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> mysql> mysql> drop table Test; Query OK, 0 rows affected (0.02 sec) mysql></source>
Using the SET type
<source lang="sql">
mysql> mysql> CREATE TABLE myset (col SET("a", "b", "c", "d")); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO myset (col) VALUES ("a,d"),
-> ("d,a"), -> ("a,d,a"), -> ("a,d,d"), -> ("d,a,d");
Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT col FROM myset; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.00 sec) mysql> mysql> drop table myset; Query OK, 0 rows affected (0.00 sec) mysql></source>