5.0 is GA (and with dynamic stored procedure SQL…!)
All -
I’m very proud of the MySQL 5.0 GA release today! Without question, our engineering team deserves a lot of accolades and pats on the back for a job very well done.
Last week, I was visiting a number of industry analyst groups and magazines, and was discussing the upcoming MySQL 5.0 GA release. It was very encouraging to hear them tell me (rather than vice-versa) what an important release this is for MySQL. Many shared with me their belief that 5.0 will open up many new areas of opportunity for MySQL that previously didn’t exist. Obviously, I think so too…
One last exciting nugget I wanted to share: dynamic SQL support in MySQL 5.0 stored procedures is now official! This means you can code dynamic SELECTs and other statements (DML, some DDL, etc.) within your stored procedures. For example, you can use the below proc to get accurate row counts for tables that may have outdated counts in the data dictionary:
CREATE PROCEDURE count_table_rows (dbname varchar(30))
DETERMINISTIC
BEGIN
DECLARE done tinyint DEFAULT 0;
DECLARE tabname varchar(64);
DECLARE asql VARCHAR(100);
DECLARE cur1 CURSOR FOR select table_name
from information_schema.tables
where table_schema=dbname
order by table_name;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO tabname;
IF NOT done THEN
SET @asql = concat("select '", tabname, " count is ' tabname," );
SET @asql = concat(@asql ," count(*) from ",tabname);
PREPARE pst FROM @asql;
EXECUTE pst;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
DEALLOCATE PREPARE pst;
END
//
Enjoy 5.0!
Robin