Looking at some of my recent posts, you can read that I am working on some MySQL that's pushing me, so please bare with me if I seem noobish...
I am wanting to employ some best practises here, and have been advised that temporary tables are a bad idea - they put extra strain on the server etc.
However, I need to query the same subset data quite a few times, and a temp table would suit me well here.
Are they a "bad thing" or do you have other opinions.
Also, I have written a stored proc to turn "a,b,d,e" into a table:
|---|-----|
|ID|VAL|
|---|-----|
|1 |a |
|2 |b |
|3 |d |
|4 |e |
|---|-----|
This creates a temp table and then throws it back, is that valid?
Code:
BEGIN
DECLARE output VARCHAR(255);
DECLARE thisvalue VARCHAR(255);
DECLARE curpos INT;
DECLARE countdelimiters INT;
SET output = '';
SET curpos = 0;
SET countdelimiters = CHAR_LENGTH(inString) - CHAR_LENGTH(REPLACE(inString, inDelim, ''));
CREATE TEMPORARY TABLE splitValues (
splitval_Num INT NOT NULL DEFAULT 0,
splitval_Val VARCHAR(50)
);
WHILE(curpos < countdelimiters) DO
SET thisvalue = SUBSTRING(inString FROM 1 FOR LOCATE(inDelim,inString)-1);
SET inString = SUBSTRING(inString FROM LOCATE(inDelim,inString)+1 FOR 100);
INSERT INTO splitValues VALUES (curpos, thisvalue);
SET curpos = curpos+1;
SET output = CONCAT(output,thisvalue,'|');
END WHILE;
INSERT INTO splitValues VALUES (curpos+1, inString);
SET output = CONCAT(output,inString);
SELECT * FROM splitValues;
DROP TABLE splitValues;
END
Question
+Dick Montage Subscriber²
Looking at some of my recent posts, you can read that I am working on some MySQL that's pushing me, so please bare with me if I seem noobish...
I am wanting to employ some best practises here, and have been advised that temporary tables are a bad idea - they put extra strain on the server etc.
However, I need to query the same subset data quite a few times, and a temp table would suit me well here.
Are they a "bad thing" or do you have other opinions.
Also, I have written a stored proc to turn "a,b,d,e" into a table:
|---|-----|
|ID|VAL|
|---|-----|
|1 |a |
|2 |b |
|3 |d |
|4 |e |
|---|-----|
This creates a temp table and then throws it back, is that valid?
Code:
Link to comment
Share on other sites
2 answers to this question
Recommended Posts