MySQL restructuring data for a VIEW
Some code that I am reworking has a table that looks like this:
--------------------------- | uid | key | val | --------------------------- | 1 | name | Aaron | | 1 | age | 28 | | 1 | iq | 50 | | 2 | name | John | | 2 | age | 17 | | 2 | iq | 110 | | 2 | geekfu | 100 | ---------------------------
And I want to create a view that looks like this:
--------------------------------------- | uid | name | age | iq | geekfu | --------------------------------------- | 1 | Aaron | 28 | 50 | null | | 2 | John | 17 | 110| 100 | ---------------------------------------
The PHP code I’m looking at is running through loops to organize the code, I am thinking there must be a simpler way. Any pointers to put start me in the right direction?
March 23rd, 2007 at 12:24 pm
well, you could go with CREATE VIEW, if you have a sufficiently new version of mysql. It’s actually quite simple.
March 23rd, 2007 at 12:28 pm
CREATE TABLE aarontbl (
uid int(11) NOT NULL,
keyfld varchar(6) DEFAULT NULL,
valfld varchar(6) DEFAULT NULL
);
INSERT INTO aarontbl (uid, keyfld, valfld) VALUES
(1,’name’,'Aaron’),
(1,’age’,'28′),
(1,’iq’,'50′),
(2,’name’,'John’),
(2,’age’,'17′),
(2,’iq’,'110′),
(2,’geekfu’,'100′);
CREATE VIEW aaronview (uid, name, age, iq, geekfu) AS
SELECT DISTINCT a.uid,
b.valfld AS name,
c.valfld AS age,
d.valfld AS iq,
e.valfld AS geekfu
FROM aarontbl AS a
LEFT JOIN aarontbl AS b ON (a.uid = b.uid AND b.keyfld = ‘name’)
LEFT JOIN aarontbl AS c ON (a.uid = c.uid AND c.keyfld = ‘age’)
LEFT JOIN aarontbl AS d ON (a.uid = d.uid AND d.keyfld = ‘iq’)
LEFT JOIN aarontbl AS e ON (a.uid = e.uid AND e.keyfld = ‘geekfu’);
March 23rd, 2007 at 12:51 pm
Lorenzo, thanks… My main problem is that I was thinking that the data values were arbitrary and not defined.
Is there any way I could add a new value row into aarontbl and then have aaronview dynamically change? Or is that just not a good idea.
March 23rd, 2007 at 2:28 pm
Aaron, I’m sorry to say, but what you want to do is impossible. The concept of a dynamically expanding VIEW just doesn’t exist - at least not in PostgreSQL or MySQL.
And Lorenzo’s suggestion gets slower and slowere which every key added.
I guess the awkward way of accessing the data in this case is the price you have to pay for not-so-perfect normalization.
Philip
March 23rd, 2007 at 4:52 pm
Philip, thanks… just for my interest what would be the proper normalization for storing arbitrary data pairs?
March 23rd, 2007 at 4:55 pm
If the view is peformance critical I’d highly recommend to dynamically do an ALTER TABLE in order to add or remove columns to a normal table, which you then fill with data. A view like the one above will become very very slow.
March 23rd, 2007 at 5:13 pm
The closest you could come is to use group_concat. group_concat is really a pretty awsome little function. make sure that the group_concat_max_len is set correctly, it defaults to 1024. But using that you can get something like:
mysql> select group_concat(keyfld, ‘:’, valfld order by keyfld) as data from aar
ontbl group by uid;
+————————————+
| data |
+————————————+
| age:28,iq:50,name:Aaron |
| age:17,geekfu:100,iq:110,name:John |
+————————————+
which is as close as you can get, but really isn’t that bad. you just have to parse it at the first colon.
Pretty cool eh?
cheers,
Joel
March 23rd, 2007 at 5:38 pm
Aaron, there’s probably more than a way to do what you want, but as Philip said they’re all slow as hell.
I’m going to show a possible solution, as a purely academical exercise, but I wouldn’t ever use it in a production environment.
You’d better recreate your table with a better normalization. Anyway, here it is:
=================================
DELIMITER //
CREATE PROCEDURE recreate_view()
BEGIN
DECLARE sql_statement1 TEXT;
DECLARE sql_statement2 TEXT;
DECLARE sql_statement3 TEXT;
DECLARE fld_name VARCHAR(256);
DECLARE done BOOL DEFAULT FALSE;
DECLARE aliasname CHAR(1);
DECLARE aliascnt INTEGER DEFAULT 66;
DECLARE cur CURSOR FOR SELECT DISTINCT keyfld FROM aarontbl;
DECLARE CONTINUE HANDLER FOR sqlstate ‘02000′ SET done = TRUE;
SET sql_statement1 = ‘CREATE OR REPLACE VIEW aaronview (uid’;
SET sql_statement2 = ‘) AS SELECT DISTINCT A.uid’;
SET sql_statement3 = ‘ FROM aarontbl AS A’;
OPEN cur;
keysloop: LOOP
FETCH cur INTO fld_name;
IF done THEN
CLOSE cur;
LEAVE keysloop;
END IF;
SELECT CHAR(aliascnt) INTO aliasname;
SET aliascnt = aliascnt + 1;
SET sql_statement1 = CONCAT(sql_statement1, ‘, ‘, fld_name);
SET sql_statement2 = CONCAT(sql_statement2, ‘, ‘, aliasname, ‘.valfld’, ‘ AS ‘, fld_name);
SET sql_statement3 = CONCAT(sql_statement3, ‘ LEFT JOIN aarontbl AS ‘, aliasname,
‘ ON (A.uid = ‘, aliasname, ‘.uid AND ‘, aliasname, ‘.keyfld = \”, fld_name, ‘\’)');
END LOOP;
SET @x = CONCAT(sql_statement1, sql_statement2, sql_statement3);
PREPARE x from @x;
EXECUTE x;
DROP PREPARE x;
END;
//
DELIMITER ;
CALL recreate_view();
SELECT * FROM aaronview;
INSERT INTO aarontbl(uid, keyfld, valfld) VALUES (2, ‘test’, ‘test’);
CALL recreate_view();
SELECT * FROM aaronview;
=================================
Since MySQL doesn’t allow dynamic SQL in stored procedures or triggers (another reason to use a proper dbms), you can’t attach to aarontbl a TRIGGER firing after an INSERT and calling recreate_view(), so you have to do it yourself before your SELECT query.
I’m sure there are better solutions, which are left as an exercise for the reader
March 23rd, 2007 at 6:51 pm
Here is a way to turn your old table into the new one :
select uid,
group_concat(if(keyfld=’name’,valfld, ”) SEPARATOR ” ) as name,
group_concat(if(keyfld=’age’,valfld, ”) SEPARATOR ” ) as age,
group_concat(if(keyfld=’iq’,valfld, ”) SEPARATOR ” ) as iq,
group_concat(if(keyfld=’geekfu’,valfld, ”) SEPARATOR ” ) as geekfu
from aarontbl group by uid;
If you want NULL instead of empty string, an extra IF will do, I guess.
I tried to see if one could export the list of columns into an extra table, but there is no way to dynamically change the requested column with MySQL. Unless there is a trick I’ve missed.