"MySQL must know" reading notes _4

PS: A real-world stored procedure case

CREATEDefiner=' Root ' @ ' localhost 'PROCEDURE' Sp_delete_article_by_id ' (inch' ID 'int)BEGIN#Routine body goes here ...DECLARE Temp INT;SET @aid =ID;SELECT COUNT(*) fromGk_reply_articleWHEREa_id= @aid  into Temp;IF(Temp <> 0) ThenDELETE  fromGk_reply_articleWHEREa_id= @aid;END IF;SELECT COUNT(*) fromGk_like_articleWHEREa_id= @aid  into Temp;IF(Temp <> 0) ThenDELETE  fromGk_like_articleWHEREa_id= @aid;END IF;SELECT COUNT(*) fromGk_articleWHEREId= @aid  into Temp;if(Temp <> 0) ThenDELETE  fromGk_articleWHEREId= @aid;END IF;END

A cursor is a database query stored on a MySQL server that is not a SELECT statement But a result set retrieved by the statement. After a cursor is stored, the application can scroll or browse its data as needed.

CREATE PROCEDUREprocessorders ()BEGINDECLAREOINT;DECLAREOrdernumbersCURSOR#定义和命名游标 forSELECTOrder_num fromorders;OPENordernumbers; #打开游标FETCHOrdernumbers intoo;CLOSEordernumbers; #关闭游标END

Implicit shutdown: If you do not explicitly close the cursor, MySQL will automatically close it when the END statement is reached.

Trigger-Unique naming

The table associated with the trigger

Trigger-response activity: Update,INSERT , or update

When triggers are executed

Triggers are supported only for tables, and views do not support

Transaction processing, which can be used to maintain the integrity of a database, ensures that a batch of MySQL operations are either fully executed or not executed at all.

MySQL is preconfigured with a set of default settings, which are usually useful at the beginning. But after a while you may need to adjust memory, buffer size, and so on. (To view the current settings, you can use show VARIABLES; and the SHOW STATUS; )

If you experience significant performance degradation, you can use show processlist to display all active processes, as well as to end a particular process with the KILL command.

Mysqldump Data Backup:

1. Export the entire database mysqldump-u user name -P database name > exported file name mysqldump-u Wcnc-p SMGP_APPS_WCNC > Wcnc.sql

2. Export a table MYSQLDUMP -U  -P  >  exported file name mysqldump -u wcnc -p smgp_apps_wcnc users>  wcnc_users.sql

3.3. export a database structure mysqldump  -u wcnc -p -d --add-drop-table smgp_apps_wcnc >d:wcnc_db.sql -d   no data   -- add-drop-table  in each create drop table 

Suppose our task is to back up a 192.168.1.2 backup_test d:\bak\ windows xp windows cmd The command is ready to start), enter the following command at the command prompt:

mysqldump -u backup -p 123456 -h 192.168.1.2 backup_test  > d:\bak\bakcup.sql MYSQLDUMP  myqldump ;-u  is the user name of the database followed by the user name Span style= "FONT-FAMILY:CALIBRI;" >backup -P  is the password of the database, followed by the same password, note is -p -H  is the address of the database, If no such entry indicates that the database is backed up locally, BACKUP_TEST 

> indicates that the database is backed up to a later file, followed by the address of the backup file of course, note that to use the absolute file path, the suffix of the file can also be used . txt. Once the command is executed,mysqldump immediately performs the backup operation. Remotely backing up a database it would take a while for a typical remote backup to look like a network.

or use Mysqlhotcopy.

Backuo TABLE or SELECT into OUTFILE dumps all data to an external file.

To ensure that all data is written to disk, you may need to use the Flush TABLES statement before making a backup .

#维护数据库, displaying the database table status

ANALYZE TABLE gk_article

# Find and fix table problems

CHECK TABLE gk_article

# Displays all available character sets and descriptions and default proofing for each character set.

SHOW CHARACTER SET

# Show all available proofing

SHOW COLLATION

Default port:3306

End