set(..,..) column type

[hvass]

I use the set column type a lot
mysetcol set("a","b","c")

Does anyone know if you can make it case sensitive
mysetcol set("a","A","b","B","c","C")

Basically I would like to get this to work ..

create table t
	(
	mycharcol char(1),
	mysetcol set("a","A","b","B","c","C")
	);
insert into t
	(mycharcol,mysetcol)
values
	("a","a"),
	("b","b"),
	("c","c"),	
	("A","A"),
	("B","B"),
	("C","C");
select
	*
from t;

 

[TonyGroves]

Can't see any way to do it. Even if you declare the field as[tt] SET("a","A","b","B","c","C") BINARY [/tt] and insert values[tt] (BINARY "a", BINARY "a") [/tt]etc., it still stores an "a" as "A". Looks to me like a bug, unless I'm missing something.

 

[hvass]

I tried the same thing putting the BINARY key word into the syntax in various places.

I dont suppose it is a bug but thanks because your comment got me to thinking - so had a look around in the source (bless Open Source) but not being a C programmer without much luck so far

 

[hvass]

I got an answer from the mysql forum on this one

http://forums.mysql.com/read.php?20,11290,11443#REPLY

mysetcol set("a","A","b","B","c","C") BINARY

you just need to add the BINARY key word at the end of the set statement rather than before or in it

 

[TonyGroves]

Here's what I get with exactly the same code as in that link:
[tt]
mysql> CREATE TABLE t
-> (
-> mycharcol CHAR(1),
-> mysetcol SET("a","A","b","B","c","C") BINARY
-> );
Query OK, 0 rows affected, 3 warnings (0.03 sec)

mysql> INSERT INTO t (mycharcol,mysetcol)
-> VALUES ("a","a"),("b","b"),("c","c"),("A","A"),("B","B"),("C","C");
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT *
-> FROM t;
+-----------+----------+
| mycharcol | mysetcol |
+-----------+----------+
| a | a |
| b | b |
| c | c |
| A | a |
| B | b |
| C | c |
+-----------+----------+
6 rows in set (0.00 sec)

mysql> show variables like 'version';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| version | 4.1.7-nt |
+---------------+----------+
1 row in set (0.00 sec)
[/tt]