mysql 记录

mysql uft 8

[mysqld] 
    collation-server = utf8_unicode_ci
    character-set-server = utf8
[client]
    default-character-set=utf8

https://bugs.launchpad.net/ubuntu/+source/mysql-5.5/+bug/958120

http://stackoverflow.com/questions/3513773/change-mysql-default-character-set-to-utf8-in-my-cnf

一个Tag表

table_template = \
        ''' CREATE TABLE {$table_name} (
            {$table_name}id int unsigned not null auto_increment,
            data_name       varchar(30)  not null,
            data_id         int unsigned not null,
            name            varchar(100) charset utf8 not null,
            primary key     ({$table_name}id),
            unique key      (data_name, data_id, name),
            key             (data_name, name)
        )ENGINE=InnoDB; '''

即1又3还adf的标签

SELECT t.data_id data_id FROM Tag t  
    where t.name = 1 or t.name=3 or t.name='adf'
    group by t.data_id having
    count(t.data_id) = 3

更新标签中不存在的某列

data_id 4有标签1 2 3 xxx11 xxx12

data_id 1有标签1 2 3 

两个data_id对应的数据都有1 2 3这三条标签，因此需要更新xxx11 xxx12这两列

update  Tag
    set name=CONCAT("xxx",Tagid)
    where Tagid 
        in 
        (select tmp.id id from
            (
                SELECT t2.Tagid id FROM  Tag t2 where t2.data_id=4 
                and t2.name not in
                (
                    SELECT t1.name FROM Tag t1 where t1.data_id = 1
                )
            ) tmp
        );

转载于:https://my.oschina.net/duoduo3369/blog/170660