MySQL_DBA资料整理

Steven_1981 · 发表于 2010-8-23 10:18

how to recovery innodb from Pages from .idb (Part IV)
http://steven1981.itpub.net/post/7967/504163

Steven_1981 · 发表于 2010-8-23 10:18

how to recovery innodb from Pages from .idb (Part V)
http://steven1981.itpub.net/post/7967/504164

jinguanding · 发表于 2010-8-29 23:24

数据库算法与数据结构PPT__URL连接：
http://www.itpub.net/thread-1342109-1-1.html

Linux Cache 机制探究_URL连接：
http://www.itpub.net/thread-1342112-1-1.html

Load和CPU利用率是如何算出来的_URL连接：
http://www.itpub.net/thread-1342098-1-1.html

大家一起来测试，benchmark起来(MySQL下的TPC-C,TPC-H,TPC-W)_URL连接：
http://www.itpub.net/thread-1330311-1-1.html

MyStat实时性能监控脚本:
http://www.itpub.net/thread-1345001-1-1.html

SQLLITE迁移到MySQL：
http://steven1981.itpub.net/post/7967/505145

Multi-Master Manager（MMM）MySQL高可用测试笔记链接：
http://www.itpub.net/thread-1350715-1-1.html

percona对MySQL标准版本改进汇总连接地址：
http://www.itpub.net/thread-1375303-1-1.html

查看MySQL性能的脚本链接：
http://www.itpub.net/viewthread. ... age%3D1#pid16442735

InnoDB internal：
http://www.itpub.net/thread-1355863-1-1.html

推荐想了解InnoDB更多更透彻的朋友，下载看此文

MySQL例外处理连接地址：

http://www.itpub.net/thread-908661-1-1.html

静态编译tcmalloc到MySQL连接地址:

http://www.itpub.net/thread-1363139-1-1.html#

捕捉所有的异常的预处理：

DECLARE CONTINUE HANDLER FOR SQLSTATE 'HY000' SET iflag=1;
DECLARE CONTINUE HANDLER FOR SQLWARNINGS    SET iflag=2;
DECLARE CONTINUE HANDLER FOR NOT FOUND          SET iflag=3;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION    SET iflag=4;

存储过程参数默认值的处理办法：
http://www.itpub.net/thread-1373300-1-1.html

Slave SQL线程阻塞时执行Slave相关命令的风险（Bug）：
http://www.itpub.net/thread-1377419-1-1.html

Slave SQL线程阻塞时执行Slave相关命令的风险（5.1.50之前版本）：
http://www.itpub.net/thread-1377419-1-1.html

Linux下安装配置GreenPlun连接地址：
http://www.itpub.net/thread-1394314-1-1.html

keepalived构建高可用MySQL-HA连接地址：
http://www.itpub.net/thread-1382523-1-1.html

MySQL UUID()函数详解连接地址：
http://www.mysqlops.com/2011/03/01/mysql-uuid.html

因JAVA程序的驱动程序版本与主服务器mysql版本相差甚大造成的主从复制问题链接地址：
http://www.itpub.net/thread-1402633-1-1.html

剖析：MySQL的左连接、右连接、等值连接异同，连接地址：
http://www.mysqlops.com/2011/03/ ... ght-inner-join.html

学习内容建议，链接地址：
http://www.itpub.net/thread-1431141-1-1.html

mysqd实例服务hang住的检测思路及方案，连接地址：
http://www.mysqlops.com/2011/05/ ... 96%B9%E6%A1%88.html

Sphinx搜索引擎架构与配置步骤，链接地址：
http://www.mysqlops.com/2011/05/ ... AD%A5%E9%AA%A4.html

[ 本帖最后由 jinguanding 于 2011-5-18 10:35 编辑 ]

jinguanding · 发表于 2010-8-30 14:43

-- Description
CREATE TABLE t1(A VARCHAR(20),B VARCHAR(20),C VARCHAR(20))ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
INSERT INTO t1(A,B,C) VALUES('1','A','1'),('2','A,B','1,2'),('3','A,B,C','1,2,3'),('4','A,B,C,D','1,2,3,4'),('5','abcde','12345');
+------+---------+---------+
| A | B    | C    |
+------+---------+---------+
| 1 | A    | 1    |
| 2 | A,B    | 1,2    |
| 3 | A,B,C | 1,2,3 |
| 4 | A,B,C,D | 1,2,3,4 |
| 5 | abcde | 12345 |
+------+---------+---------+
5 rows in set (0.00 sec)

TO ---->
+------+-------+-------+
| A | B    | C    |
+------+-------+-------+
| 1 | A    | 1    |
| 2 | B    | 2    |
| 2 | A    | 1    |
| 3 | C    | 3    |
| 3 | B    | 2    |
| 3 | A    | 1    |
| 4 | D    | 4    |
| 4 | C    | 3    |
| 4 | B    | 2    |
| 4 | A    | 1    |
| 5 | abcde | 12345 |
+------+-------+-------+

-- Code
DELIMITER $$
DROP FUNCTION IF EXISTS `fuc_split` $$
CREATE FUNCTION fuc_split(strValue VARCHAR(20),strSplit CHAR(1),iPostion TINYINT)
RETURNS VARCHAR(10) DETERMINISTIC
BEGIN
DECLARE strValue_New  VARCHAR(20) DEFAULT '';
DECLARE strValue_Old  VARCHAR(20) DEFAULT '';
DECLARE strReturn    VARCHAR(10) DEFAULT '';

DECLARE iLength_Old TINYINT DEFAULT 0;
DECLARE iLength_New TINYINT DEFAULT 0;
DECLARE iLength    TINYINT DEFAULT 0;

SET strValue_New=SUBSTRING_INDEX(strValue,strSplit,iPostion);
SET strValue_Old=SUBSTRING_INDEX(strValue,strSplit,iPostion-1);
SET iLength_New=LENGTH(strValue_New);
IF iPostion-1<>0 THEN
   SET iLength_Old=LENGTH(strValue_Old);
ELSE
   SET iLength_Old=-1;
END IF;
SET iLength=iLength_New-iLength_Old;

SET strReturn=SUBSTR(strValue_new,iLength_Old+2,iLength);

RETURN strReturn;
END $$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS `usp_split_insert` $$
CREATE PROCEDURE usp_split_insert()
BEGIN
DECLARE strValue_A  VARCHAR(20) DEFAULT '';
DECLARE strValue_B  VARCHAR(20) DEFAULT '';
DECLARE strValue_C  VARCHAR(20) DEFAULT '';

DECLARE strValue_B_tmp  VARCHAR(20) DEFAULT '';
DECLARE strValue_C_tmp  VARCHAR(20) DEFAULT '';

DECLARE iLength_Old TINYINT DEFAULT 0;
DECLARE iLength_New TINYINT DEFAULT 0;
DECLARE iLength    TINYINT DEFAULT 0;
DECLARE iFlag    TINYINT DEFAULT 0;

DECLARE cur_t1 CURSOR FOR SELECT A,B,C FROM T1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET iFlag=1;

DROP TEMPORARY TABLE IF EXISTS `tmp_t1`;
CREATE TEMPORARY TABLE tmp_t1(A VARCHAR(20),B VARCHAR(20),C VARCHAR(20))ENGINE=MEMORY CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

OPEN cur_t1;
FETCH cur_t1 INTO strValue_A,strValue_B,strValue_c;

WHILE iFlag<>1
DO
   SET iLength_Old=LENGTH(strValue_B);
   SET iLength_New=LENGTH(TRIM(REPLACE(strValue_B,',','')));

   SET iLength=iLength_Old-iLength_New+1;
   WHILE iLength<>0
   DO

      SET strValue_B_tmp=fuc_split(strValue_B,',',iLength);
      SET strValue_C_tmp=fuc_split(strValue_C,',',iLength);
      INSERT INTO tmp_t1(A,B,C) VALUES(strValue_A,strValue_B_tmp,strValue_C_tmp);

      SET iLength=iLength-1;
   END WHILE;

   SET iLength=0;
   SET iLength_Old=0;
   SET iLength_New=0;

   FETCH cur_t1 INTO strValue_A,strValue_B,strValue_c;
END WHILE;

SELECT * FROM tmp_t1;
END $$
DELIMITER ;

-- testing
CALL  usp_split_insert();