MySQL5新特点(触发器)
MySQL从5.0.2版开始引入触发器,触发器就是一个已命名的数据库对象,这个对象和某张表 相关,而且当这张表发生某种特定事件后,触发器将被激活执行相应的动作,触发器允许这 些动作在这张表中的一行或多行的数据被操作的前后执行。在流行业务系统的处理过程中
MySQL从5.0.2版开始引入触发器,触发器就是一个已命名的数据库对象,这个对象和某张表 相关,而且当这张表发生某种特定事件后,触发器将被激活执行相应的动作,触发器允许这 些动作在这张表中的一行或多行的数据被操作的前后执行。在流行业务系统的处理过程中,开发和管理人员可以用触发器来实现数据审计和其他安全相关的功能,如在运行中的数据执行加密功能。举个例子来说:一个客户数据库中包含客户的社会保险号,企业的安全和审计人员必须将这些信息加密后存入磁盘。针对这种情况,管理员可以通过创建一个触发器来自动获取并加密这些数据然后再插入相应的数据库表中。如下所示:
mysql> delimiter // mysql> create trigger t_customer_insert before insert on customer -> for each row -> begin -> set NEW.customer_ssn = aes_encrypt(NEW.customer_ssn,'password'); -> end; -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> insert into customer values (1,'fred','smith','456097234'); Query OK, 1 row affected (0.00 sec) mysql> select * from customer;

可以看出存在磁盘数据库表中的社会保险号ssn已经变成加密后的不可读乱码格式。
另外MySQL5也引进了对游标的支持,在第一个发行版本中游标有向前翻阅结果集和不可执行数据更新语句的特点。游标可以用在MySQL5的新编码对象,如存储过程和触发器,甚至是独立的存储过程逻辑块中。以下是一个在存储过程中的使用游标的简单例子:
mysql> delimiter // mysql> CREATE PROCEDURE cursor_demo() -> BEGIN -> DECLARE a, b CHAR(16); -> DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1; -> OPEN cur1; -> REPEAT -> FETCH cur1 INTO a,b; -> UNDONE END REPEAT; -> CLOSE cur1; -> END -> //
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account -> FOR EACH ROW SET @sum = @sum + NEW.amount;
(51CTO.COM教程)

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Big data structure processing skills: Chunking: Break down the data set and process it in chunks to reduce memory consumption. Generator: Generate data items one by one without loading the entire data set, suitable for unlimited data sets. Streaming: Read files or query results line by line, suitable for large files or remote data. External storage: For very large data sets, store the data in a database or NoSQL.

MySQL query performance can be optimized by building indexes that reduce lookup time from linear complexity to logarithmic complexity. Use PreparedStatements to prevent SQL injection and improve query performance. Limit query results and reduce the amount of data processed by the server. Optimize join queries, including using appropriate join types, creating indexes, and considering using subqueries. Analyze queries to identify bottlenecks; use caching to reduce database load; optimize PHP code to minimize overhead.

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to insert data into MySQL table? Connect to the database: Use mysqli to establish a connection to the database. Prepare the SQL query: Write an INSERT statement to specify the columns and values to be inserted. Execute query: Use the query() method to execute the insertion query. If successful, a confirmation message will be output.

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the "MySQL Native Password" plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

To use MySQL stored procedures in PHP: Use PDO or the MySQLi extension to connect to a MySQL database. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.

Creating a MySQL table using PHP requires the following steps: Connect to the database. Create the database if it does not exist. Select a database. Create table. Execute the query. Close the connection.

Oracle database and MySQL are both databases based on the relational model, but Oracle is superior in terms of compatibility, scalability, data types and security; while MySQL focuses on speed and flexibility and is more suitable for small to medium-sized data sets. . ① Oracle provides a wide range of data types, ② provides advanced security features, ③ is suitable for enterprise-level applications; ① MySQL supports NoSQL data types, ② has fewer security measures, and ③ is suitable for small to medium-sized applications.
