mysqluc-2006-tutorial-sp

Description

MySQL 5.1Past, Present and F utureJan KneschkeMySQL ABAgenda• Past• S Q L Trees m eets D ynam ic S Q L• P resent• E vents• P artitioning• Future• V ertical P artitioningAbout the Presenter●Jan Kneschke●Senior Developer in the MySQL Network Team● jan@mysql.c om●Drives the development of the high-performance web-server lighttpd●Was trainer and consultant at MySQL before becoming a developerRaise 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• F irst available in MySQL 4.1• S plit up the execution of S Q L statem ents into a P R E P A R E and a E X E C U TE phase• A t best P R E P A R E once, E X E C U TE m ultiple tim es• P revents S Q L-injectionPrepared StatementsPREPARE 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 ses the syntax defined in the SQ L:2003 standard• Provides C ontrol-Flow , Loops, Exceptions, C ursors, ...• SPs are used by Triggers, Events, FunctionsStored ProceduresCREATE PROCEDURE fill_table ()BEGIN DECLARE n INT ...

Informations

• Publié par: Zikes
• Nombre de lectures: 50
• Langue: English

Extrait

MySQL 5.1 Past, Present and Future

Jan Kneschke MySQL AB

Agenda

•

•

•

Past

• S  Q L T r e e s m

P r e s e n t

• E  v e n t s

• Partitioning

F u t u r e

•

e e t s D

Vertical Partitioning

y n a m

ic S

Q

L

About the Presenter

● Jan Kneschke

● Senior Developer in the MySQL Network Team

● [e-mail]

● Drives the development of the high- performance web-serverlighttpd

● 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 t h e e x e c u t io n o f S Q L s t a t e m e n t s in t o 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 b e s t P R E P A R E o n c e , E X E C U T E m u lt ip le t im e s

P r e v e n t s S Q L -in je c t io 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 t h e s y n t a x d e f in e d in t h e S Q L :2 0 0 3 s t a n d a r d

P r o v id e s C o n t r o l-F lo w , L o o p s , E x c e p t io 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 t s , F u n c t io 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  _ p; LEAVE ins loo  END IF;  END LOOP ins loop; _ END$$

Dynamic SQL

•

•

•

•

fill_table() works only against one table

SQL Statements in SPs are static

P r e p a r e d S t a t e m e n t s c a n o n ly u s e p la c e h o ld e r s f o r v a lu e s , n o t f o r p a r t s o f t h e SQL statement itself

PREPARE takes a constant string or (since  MySQL 5.0.13) a user variable

Dynamic SQL

SET @s = CONCAT(

_  "INSERT INTO ", tbl name,

_  " (", field name,") VALUES (?)");

PREPARE s FROM @s;

SET @n = n;

EXECUTE s USING @n;

DEALLOCATE PREPARE s;