MySQL5 new features _ MySQL

Introduction to new features of MySQL 5.0 the new feature tutorial is written for old MySQL users who need to know the new features of MySQL 5.0. Simply put, I introduced the "stored procedure, trigger, view, and information architecture view". I would like to thank the translator Chen Pengyi for his efforts.

I hope this book will be able to talk to you like an expert, so that you can learn the required knowledge with simple questions and examples. In order to achieve this purpose, I will gradually establish concepts for everyone from every detail, and finally show you a large practical example, before learning, you may think that this case is very difficult, but as long as you follow the course, I believe you will be able to master it soon.

Conventions and Styles Conventions and programming Styles

Every time I want to demonstrate the actual code, I will adjust the code displayed on the screen of the mysql client and change the font to Courier to make them look different from the normal text.

Here is an example: mysql> drop function f; Query OK, 0 rows affected (0.00 sec)

If the instance is large, you need to add comments between some rows and paragraphs. at the same time, I will place the '<--' symbol on the right side of the page to emphasize.

For example:

Mysql> create procedure p ()

-> BEGIN

->/* This procedure does nothing */<--

-> END; // Query OK, 0 rows affected (0.00 sec)

Sometimes I will remove the "mysql>" and "->" systems in the example. you can copy the code directly to the mysql client program (if you are not reading an electronic version, you can download related scripts on the mysql.com website. Therefore, all the examples have passed the test on Suse 9.2 Linux and Mysql 5.0.3 public edition.

When you read this book, Mysql has a higher version and supports more operating systems, including Windows, Linux, and HP-UX. So the example here will run normally on your computer. However, if the operation still fails, you can consult a senior Mysql User you know for long-term support and help.

A Definition and an Example Definition and Example

Definition and instance stored procedure is a program stored in the library (just like a subroutine in a regular language). To be precise, MySQL supports the "routines (routine) there are two types: one is the stored procedure, and the other is the function that can be returned in other SQL statements (the same as the Mysql preload function, such as pi ()). I will use stored procedures more frequently in this book, because this is our past habit and I believe everyone will accept it.

A stored procedure includes a name, a list of parameters, and a set of SQL statements that can contain many SQL statements.

Here, a new syntax is defined for local variables, exception handling, loop control, and IF clauses.

The following is an example of a stored procedure statement)

Create procedure procedure1/* name stored PROCEDURE name */

(IN parameter1 INTEGER)/* parameters parameter */

BEGIN/* start of block statement header */

DECLARE variable1 CHAR (10);/* variables variable declaration */

IF parameter1 = 17 THEN/* start of IF condition start */

SET variable1 = 'Birds ';/* assignment value */

ELSE

SET variable1 = 'Beasts';/* assignment value */

End if;/* end of if end */

Insert into table1 VALUES (variable1);/* statement SQL statement */

END/* end of block statement block END */

Next I will introduce all the details about the work you can do using stored procedures. At the same time, we will introduce the new database object-trigger, because the association between the trigger and the stored procedure is inevitable.

Why does Why Stored Procedures use Stored Procedures?

Because the stored procedure is a new feature for MySQL, you need to pay more attention to it during use.

After all, no one has used it before, and there are not many experienced users to take you through their path. However, you should begin to consider transferring existing programs (probably in server applications, user-defined functions (UDFs), or scripts) to the stored procedures. You do not need to do this for any reason.

Because stored procedures are already certified technologies! Although it is new in Mysql, functions of the same function already exist in other DBMS, and their syntax is always the same. So you can get these concepts from others, there are many experienced users you can consult or hire, and there are many third-party documents for you to read.

Stored procedures make the system run faster! Although we cannot prove this advantage in Mysql for the time being, the user experience is different. What we can say is that the Mysql server has improved the cache mechanism, just like what Preparedstatements does. Because there is no compiler, SQL Stored procedures do not run as quickly as external languages (such as C. However, the main way to speed up is to reduce network information traffic. If you want to handle repetitive tasks that require check, loop, and multi-statement operations without user interaction, you can use the stored procedure on the server. In this way, there will be less information between the server and the client at each step of the task.

Therefore, stored procedures are reusable components! Imagine if you change the host language, this will not affect the stored procedure, because it is a database logic rather than an application. Stored procedures can be transplanted! When you use SQL to write a stored procedure, you will know that it can run on any platform supported by Mysql. you do not need to add additional runtime environment packages, you do not need to set the permission for the program in the operating system, or the stored procedure for your different models of computers will be saved! If you have compiled a program, such as displaying the withdrawal of check in bank transaction processing, you can find your program for anyone who wants to know the check.

It is stored in the database as source code. This will make a meaningful association between data and the process of data processing. This may be the same as what you said in the planning theory you have heard in your class. Stored procedures can be migrated!

Mysql fully supports the SQL 2003 standard. Some databases (such as DB2 and Mimer) are also supported. However, some of them are not supported, such as Oracle and SQL Server. We will provide enough help and tools to make it easier for the code written for other DBMS to be transferred to Mysql.

Setting up with MySQL 5.0 Setting and starting MySQL 5.0 service

Pass

Mysql_fix_privilege_tables

Or

~ // Mysql-5.0/scripts/mysql_install_db

To start the MySQL service

As part of our exercise preparation, I assume MySQL 5.0 has been installed. If you do not have a database administrator to install the database and other software for you, you need to install it on your own. But one thing you can easily forget is that you need a table named mysql. proc.

After the latest version is installed, you must run

Mysql_fix_privilege_tables

Or

Mysql_install_db

(You only need to run one of them) -- otherwise, the stored procedure will not work. I also enable an informal SQL script after the root identity, as shown below:

Mysql> source/home/fig/mysql-5.0/scripts/mysql_prepare_privilege_tables_for_5. SQL

Starting the MySQL Client starts the MySQL Client

This is how I start the mysql client. You may use other methods. if you are using a binary or Windows computer, you may run the following programs in other subdirectories:

Easy @ phpv: ~> /Usr/local/mysql/bin/mysql -- user = root

Welcome to the MySQL monitor. Commands end with; or/g.

Your MySQL connection id is 1 to server version: 5.0.3-alpha-debug

Type 'help; 'or'/h' for help. type'/C' to clear the buffer.

In the demo, I will display the results returned by the mysql client after login as root, which means that I have great privileges.

Check for the Correct Version

To confirm that the MySQL version is correct, we need to query the version. I have two methods to confirm that I am using version 5.0:

Show variables like 'version ';

Or

Select version ();

For example:

Mysql> show variables like 'version ';

+ --------------- + ------------------- +

| Variable_name | Value |

+ --------------- + ------------------- +

| Version | 5.0.3-alpha-debug |

+ --------------- + ------------------- +

1 row in set (0.00 sec)

Mysql> select version ();

+ ------------------- +

| VERSION () |

+ ------------------- +

| 5.0.3-alpha-debug |

+ ------------------- +

1 row in set (0.00 sec)

When you see the number '5. 0. X', you can confirm that the stored procedure works on this client.

The Sample "Database" Sample Database

The first thing we need to do now is to create a new database and set it as the default database. the SQL statement for implementing this step is as follows:

Create database db5;

USE db5;

For example:

Mysql> create database db5;

Query OK, 1 row affected (0.00 sec)

Mysql> USE db5;

Database changed

Avoid using the actual database with important data here and then create a simple worksheet.

The SQL statement for this step is as follows:

Mysql> create database db5;

Query OK, 1 row affected (0.01 sec)

Mysql> USE db5;

Database changed

Mysql> create table t (s1 INT );

Query OK, 0 rows affected (0.01 sec)

Mysql> insert into t VALUES (5 );

Query OK, 1 row affected (0.00 sec)

You will find that only one column is inserted in the table. The reason for this is that I want to keep the table simple, because here I don't need to show the data query skills, but to teach the stored procedure without using large data tables, because it is complicated enough.

This is the example database. we will select the separator for Pick a Delimiter from the table whose name is t and only contains one column.

Now we need a separator. the SQL statement to implement this step is as follows:

DELIMITER //

For example:

Mysql> DELIMITER //

The delimiter is the character or string symbol that notifies the mysql client that you have entered an SQL statement. We have always used semicolons (;), but in the stored procedure, this may cause many problems, because there are many statements in the stored procedure, therefore, each one requires a semicolon, so you need to select a string that is unlikely to appear in your statement or program.