mysql 特殊语法测试

#测试mysql的replace语句&insert into **** on duplicate key update *** 语句
#准备：
drop table testreplace;
create table testreplace
(
 seqId int unsigned not null auto_increment comment 'sequence key',
 name varchar(10) not null default '' comment 'unique key',
 value int unsigned not null default 0 comment 'unique key',
 count int unsigned not null default 1,
 primary key (seqId),
 unique key (name, value)
);

insert into testreplace (name, value, count) values ('test01', 1, 10);
insert into testreplace (name, value, count) values ('test02', 2, 20);
insert into testreplace (name, value, count) values ('test03', 1, 30);
insert into testreplace (name, value, count) values ('test04', 3, 40);

select * from testreplace;

replace testreplace set name='test01', value=3;
#unique key 是 name value，期望得到结果是insert新值

#得到新值seqId=5，count默认

replace testreplace set name='test01', value=1;
#unique key 冲突，replace结果如下：

#原seqId=1的行被删除，得到一个新行seqId=6
#解释：Replace 在解决冲突时，先执行delete，再执行insert

insert into testreplace (name, value, count) VALUE ('test02', 4, 100) on duplicate key UPDATE count=100;
#没有冲突，期望inset新行

insert into testreplace (name, value, count) VALUE ('test02', 2, 100) on duplicate key UPDATE count=100;
#unique key 冲突

#原seqId=2行被update count=100

#other
#Insert into table_name select from ***
drop table testreplace2;
create table testreplace2
(
 seqId int unsigned not null auto_increment comment 'sequence key',
 name varchar(10) not null default '' comment 'unique key',
 #value int unsigned not null default 0 comment 'unique key',
 count int unsigned not null default 1,
 primary key (seqId)
 #unique key (name, value)
);

insert into testreplace2 (seqId, name, count) select seqId, name, count from testreplace;
select * from testreplace2;

#Testreplace2的数据时从testreplace里拷贝来的
insert into testreplace2 (name, count) select name, 200 from testreplace;

#Select的时候可以补充其他值，这里为避免seqId冲突，所以没有标seqId

#MySQL不支持Select Into语句直接备份表结构和数据,以后装个Oracle试试
create table testreplace3 (select seqId, name, count from testreplace);
#或者
create table testreplace4 as select seqId, name, count from testreplace;
#可以建表插入，但是….

#没有保留testreplace的主键、索引等。

#测试concat相关
drop table testConcat;
create table testConcat
(
	id int unsigned not null,
	name varchar(20) not null
);

insert into testConcat values (1, 'aaa'), (1, 'bbb'), (1, 'bbb'), (2, 'ccc'), (3, 'ddd'), (3, 'eee');

select * from testConcat;

select id, GROUP_CONCAT(name order by name desc SEPARATOR '|') from testConcat group by id;

#DISTINCT 去除重复
select id, GROUP_CONCAT(DISTINCT name SEPARATOR '|') from testConcat group by id;