MySQL 5.1
Jan Kneschke MySQL AB
Past, Present and Future
Agenda
Past S Q L T r e e s m e e ts D y n a m ic S Q L P re s e n t E v e n ts P a r titio n in g F u tu r e V e r tic a l P a r titio n in g
About the Presenter
●
Jan Kneschke Senior Developer in the MySQL Network Team jan@mysql.com
●
● ●
Drives the development of the highperformance web-server lighttpd Was trainer and consultant at MySQL before becoming a developer
●
Raise your hands
● ● ●
Who is using MySQL 4.1 or older Who is using MySQL 5.0, 5.1 or later Who uses Prepared Statements Stored Procedures Partitioning
● ● ● ●
Who is using one of the features with another vendors RDBMS
Back in Time
●
Last years tutorial concentrated on Stored Procedures, VIEWs and Triggers Examples can be found at http://jan.kneschke.de/projects/mysql/sp/ Who has attended last years tutorial
● ●
●
Prepared Statements
First available in MySQL 4.1 S p lit u p th e e x e c u tio n o f S Q L s ta te m e n ts in to a P R E P A R E a n d a E X E C U T E p h a s e A t best P R E P A R E once, E X E C U TE m u ltip le tim e s P r e v e n ts S Q L -in je c tio n
Prepared Statements
PREPARE s FROM 'SELECT * FROM tbl WHERE id = '; EXECUTE s USING @id; DEALLOCATE PREPARE s;
Stored Procedures
A programming language running in the context of the DBMS U s e s th e s y n ta x d e fin e d in th e S Q L :2 0 0 3 s ta n d a r d P r o v id e s C o n tr o l-F lo w , L o o p s , E x c e p tio n s , C u r s o r s , ... S P s a r e u s e d b y T r ig g e r s , E v e n ts , F u n c tio n s
Stored Procedures
CREATE PROCEDURE fill_table () BEGIN DECLARE n INT DEFAULT 1000; ins_loop: LOOP INSERT INTO tbl VALUES (n); SET n = n - 1; IF (NOT n) THEN LEAVE ins_loop; END IF; END LOOP ins_loop; END$$
Dynamic SQL
fill_table() works only against one table S Q L S ta te m e n ts in S P s a r e s ta tic

下一页