MySQL语句实现字段拆分成多行

数据表`sp_raw` 结构及数据示例

其中`kw`是要拆分为多行的数据,分隔符为 " | ",管道符及前后空格共计三个字符

CREATE TABLE `sp_raw` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `doctor` int(11) NOT NULL,
  `source` int(11) DEFAULT NULL,
  `kw` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `doctor_id` (`doctor`),
  KEY `source_id` (`source`)
);

辅助表`sp_idx`

事实上只是个从1到65535的一列数据,下面要用。

CREATE TABLE `sp_idx` (
  `id` smallint(5) unsigned NOT NULL,
  `x` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`id`)
) COMMENT='拆分辅助表';
INSERT into `sp_idx`(`id`,`x`) values(1,0);
INSERT into `sp_idx`(`id`,`x`) values(2,0);
INSERT into `sp_idx`(`id`,`x`) values(3,0);
...
INSERT into `sp_idx`(`id`,`x`) values(65535,0);

对每行记录计算拆分条数

计算每行要拆分成的行数据,即有多少条数据通过分隔符拼接在一起。即计算字符串中删除分隔符后长度的减少量,即是 分隔符的个数*分隔符长度。分隔符个数+1 即是拆分条数。

这里使用char_length()计算长度,如果分隔符没有多字节字符,使用length()也可以。

SELECT r.`id`, `doctor`, `source`, `kw`,
 (char_length(kw)-char_length(REPLACE(kw,' | ',''))) DIV char_length(' | ') + 1 as kw_count 
FROM `sp_raw` r ;

联入入辅助表

按上步计算出的kw_count,联入相应条数的序列数据,从1 到 kw_count,序列数据从辅助表取。联表关系使用 < ,而不是通常使用的等于;这是本方案的精妙之处。

SELECT r.`id`, `doctor`, `source`, `kw`
 ,x.`id`
FROM `sp_raw` r
INNER JOIN `sp_idx` x 
on x.`id` <= (char_length(kw)-char_length(REPLACE(kw,' | ',''))) DIV char_length(' | ') + 1
;

按序号取子串

结果记录集最后一列id,事实上就是原始字符的排序号。每条记录按该序号截取子串即是结果了。这里使用 SUBSTRING_INDEX() 函数,截取前N段或后N段子串。

第一步,如下,截前N段子串的结果,再截最后一段即是每行所需结果。

SELECT r.`id`, `doctor`, `source` , `kw`
 ,x.`id`, substring_index(`kw`,' | ',x.`id`) as step_1
 ,substring_index(substring_index(`kw`,' | ',x.`id`),' | ',-1) as step_2
FROM `sp_raw` r
INNER JOIN `sp_idx` x 
on x.`id` <= (char_length(kw)-char_length(REPLACE(kw,' | ',''))) DIV char_length(' | ') + 1
;

总结整个过程

SELECT r.`id`, `doctor`, `source`
,x.`id` as idx, substring_index(substring_index(`kw`,' | ',x.`id`),' | ',-1) as piece
FROM `sp_raw` r 
INNER JOIN `sp_idx` x 
ON x.`id` <= (char_length(kw)-char_length(REPLACE(kw,' | ',''))) DIV char_length(' | ') + 1 ;

语句,可以当作模板使用,其中`kw` 替换为待拆分字段,' | ' 为分隔字符串,piece为结果,idx为piece在原始`kw`中的序号。

辅助表`sp_idx`需要事先建好,事实上只要从是从1开始的整数序列即可;如果待拆分子串数超过65535,则要扩充本表的记录数。INSERT into `sp_idx`(x) SELECT `x` FROM `sp_idx`;

如果实在不方便建辅助表,可以使用`mysql`. `help_topic`表的help_topic_id字段(不推荐)。要能保证其完整性,而且待拆分字符串不大于`help_topic`的其行数。而且还要加个where条件 help_topic_id>0,否则会拆分出多余的空串行,因为该字段是从0开始编号的。

附件:原始示例数据

原始示例数据备份下载 split_field_to_lines_in_mysql.zip

SQL Server中truncate、delete和drop的异同点

 相同点:

truncate和不带where子句的delete,以及drop都会删除表内的数据

 不同点:

1. truncate和delete只删除数据不删除表的结构(定义)

drop语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index);依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

2. delete语句是数据库操作语言(dml),这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发。

truncate、drop是数据库定义语言(ddl),操作立即生效,原数据不放到rollback segment中,不能回滚,操作不触发trigger。

3.delete语句不影响表所占用的extent,高水线(high watermark)保持原位置不动。

显然drop语句将表所占用的空间全部释放。

truncate语句缺省情况下见空间释放到minextents个extent,除非使用reuse storage;truncate 会将高水线复位(回到最开始)。

4.速度,一般来说: drop> truncate > delete

5.安全性:小心使用 drop和truncate,尤其没有备份的时候.否则哭都来不及。

使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大.

想删除表,当然用drop

想保留表而将所有数据删除,如果和事务无关,用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。

如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据。

来源 http://www.cnblogs.com/fengjun19912/articles/308418.html

数据库中乱码记录查找与删除/sql server剔除乱码行

乱码一般都是正常编码被错误的解析而导致。每个错误的编码,都会对应着一个正确的编码,也就是,出现频率高的乱码字符,其实就是出现高频字符(一般都是汉字)对应的乱码。
所以说,如果找到高频字符对应的乱码,在sql表里,使用like 查询即可以找到乱码行。
也就是说,查找到一系列高频乱码字符,就可以很容易的查找到绝大多数乱码行。
由于有乱码的表里存储的数据可能是不同行业、不同领域的信息,高频汉字可能不尽相同。(生命科学领域里,细、胞、蛋、白、质、核、酸等这些字频率会非常高;而计算机领域里,内、存、储、变、量、进、程等汉字会是调频汉字;其它领域亦类似)。
所以查找剔除乱码记录时,最好是整理一个最准确乱码字符表。
下面给出一个整理乱码表比较直接、有效的方法(是笔者所探索发现的方法):
1. 找到一定数量的乱码记录(建议在200条以上)
2. 把这些乱码整理出来,并拆分成单字,并统计每个单字出现频率。(建议使用excel+UE/editplus等工具)
3. 取最高频的50个单字(这里当然是要找明显是乱码的单字;也可以不是50个字,80个100个也行)
4. 使用UE或excel生成对应的50个sql like语句,形式如下
select * from [乱码表] where [乱码字段] like '%锛%'
select * from [乱码表] where [乱码字段] like '%绂%'
select * from [乱码表] where [乱码字段] like '%脠%'
select * from [乱码表] where [乱码字段] like '%┖%'
select * from [乱码表] where [乱码字段] like '%锲%'
......

为了查询方便,最把生成一个union语句,如下
select * from [乱码表] where [乱码字段] like '%悎%' union
select * from [乱码表] where [乱码字段] like '%灏%' union
select * from [乱码表] where [乱码字段] like '%楁%' union
select * from [乱码表] where [乱码字段] like '%闀%' union
select * from [乱码表] where [乱码字段] like '%寰%' union
select * from [乱码表] where [乱码字段] like '%阍%' union
.....
5. 使用上步生成的sql语句查询,查询到结果基本上全部都是乱码行(把乱码记录复制下来,下一步有用),把这些行的主键ID号复制粘贴到ue/editplus,整理成一个逗号拼接形式,就可以使用一个in语句删除
delete from [乱码表] where [主键id] in(num1,num2,num3,...)
[如果上步语句查询结果里有非乱码行,则明记下非乱码行的ID号,在生成delete ... in ... 语句时,剔除这些ID号,就可保证万无一失]
6. 如果需要更进一步删除乱码,可对上一步记下的乱码记录做以下步骤:
单字分隔-统计词频-取法高频乱码单字-整理sql like语句-查询-确认-生成delete... in...语句-删除
这一过程可以继续多次进行,直至没有乱码行为止。

[注]
1. “乱码”只是习惯说法。乱码单字其实也都是正常的字符——多半是比较生僻的汉字,类似于时下流行已久的“火星文”。其中当然也有一些常见的汉字。
2. 上面提到清理乱码方法是提到的"200条乱码记录"、"50个乱码单字"是笔者操作中使用,你也可以增减这些数目,以达到最佳的清理效果。

变态的Access2007:在Access2007中创建SQL传递查询(执行SQl语句)

1. 在“创建”选项卡中,单击“其他”组中的“查询设计”。
2. 单击“显示表”对话框中的“关闭”,而不添加任何表或查询。
3. 在“设计”选项卡中,单击“查询类型”工作组中的“传递”。
4. 单击“显示/隐藏”工作组中的“属性表”以显示查询的属性表。
5. 在查询的属性表中,将鼠标指针置于“ODBC 连接字符串”属性中,然后单击“生成”(...) 按钮。
利用“ODBC 连接字符串”属性,可以指定与要连接的数据库有关的信息。可以键入连接信息,或者单击“生成”,然后输入与要连接的服务器有关的信息。
6. 当提示您是否在连接字符串中保存密码时,如果希望将密码和登录名存储在连接字符串信息中,请单击“是”。
7. 如果查询不属于可返回记录的类型,请将“ReturnsRecords”属性设置为“No”。
8. 在“SQL 传递查询”窗口中,键入您的传递查询。例如,下面的传递查询在 Select 语句中使用 Microsoft SQL Server TOP 运算符,以仅返回示例数据库的“订单”表中的前 25 份订单:
Select TOP 25 orderid from orders
9. 若要运行查询,请单击“设计”选项卡的“结果”组中的“运行”。对于返回记录的 SQL 传递查询,请单击状态栏上的“数据表视图”。
10. 如果需要,Microsoft Access 将提示您输入有关服务器数据库的信息