$link->query("DROP TABLE IF EXISTS table2");
$link->query("CREATE TABLE table2 (newcol BIGINT UNSIGNED PRIMARY KEY)");
$result=$link->query("select col1 from table1");
while($data=$result->fetch_array(MYSQL_ASSOC))
{
$link->query("insert into table2 (newcol) values($data['col1']);
$link->query(""ALTER TABLE table2 ADD `".$data['col1']."` BIGINT DEFAULT 0"");
}
我想做的是
- 创建一个表"
table2
",其中一列"newcol
" - 从"
table1
"中选择所有"col1
"的值, 对于
table1
中col1
的每个值-插入
table2
的"newcol
" And-添加
named
列(table 1
的col1
中的value
)到"table2
"
上面的代码在php中看起来非常整洁和高效,但问题是它需要一些时间。所以我认为最好将这些转换为MySQL存储过程。因为我是存储过程的新手,非常困惑。请帮助我的家伙。
当然,我无法测试它,但它在我的计算机上编译得很好。
DELIMITER //
CREATE PROCEDURE `myProcedure` ()
BEGIN
DECLARE _done BOOLEAN DEFAULT FALSE;
DECLARE _myField BIGINT UNSIGNED DEFAULT 0;
/* the cursor here is like your PDOStatement
* it is used to fetch data */
DEClARE _myReader CURSOR FOR
SELECT `col1` FROM `table1`;
/* it is not very elegant, but we need to throw an exception
* to break the loop */
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET _done = TRUE;
DROP TABLE IF EXISTS `table2`;
CREATE TABLE `table2` (
`newcol` BIGINT UNSIGNED PRIMARY KEY
);
/* you open your PDOStatement */
OPEN _myReader;
/* myLoop is like a GOTO*/
myLoop: LOOP
/* $result->fetch_array(MYSQL_ASSOC)*/
FETCH _myReader INTO _myField;
/* if the no data exception had been thrown,
* goto the end of the loop */
IF _done = 1 THEN
LEAVE myLoop;
END IF;
INSERT INTO `table2` (newcol) VALUES (_myField);
ALTER TABLE `table2` ADD `_myField` BIGINT DEFAULT 0;
END LOOP myLoop;
/* close your PDO object */
CLOSE _myReader;
END //
Jonathan Parent l
谢谢Jonathan Parent lsamuvesque
但是在他的代码中,使用变量添加列名并没有像预期的那样工作。
我终于想通了
BEGIN
DECLARE _done BOOLEAN DEFAULT FALSE;
DECLARE _myField BIGINT DEFAULT 0;
DEClARE _myReader CURSOR FOR
SELECT id FROM `tags`;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET _done = TRUE;
DROP TABLE IF EXISTS `tag_similarity`;
CREATE TABLE `tag_similarity` (
`tag` BIGINT UNSIGNED PRIMARY KEY
);
OPEN _myReader;
myLoop: LOOP
FETCH _myReader INTO _myField;
IF _done = 1 THEN
LEAVE myLoop;
END IF;
INSERT INTO `tag_similarity` (tag) VALUES (_myField);
SET @sql = CONCAT('ALTER TABLE tag_similarity ADD `',_myfield,'` BIGINT DEFAULT 0');
PREPARE stmt FROM @sql;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;
END LOOP myLoop;
CLOSE _myReader;
END