:: DEVELOPER ZONE
FEDERATED
storage engine, new in MySQL 5.0, allows the server to access tables in other (remote) servers. See http://dev.mysql.com/doc/mysql/en/federated-storage-engine.html
ARCHIVE
Storage Engine, added in MySQL 4.1, is ideally suited for storing large amounts of data (without indexes) in a very small footprint, and selects using table scans. See http://dev.mysql.com/doc/mysql/en/archive-storage-engine.html for details.
INFORMATION_SCHEMA
, precision math (DECIMAL
column type), and the BIT
column type, apply to all storage engines. There are also additions and changes for specific storage engines.
SET [SESSION|GLOBAL] sql_mode='modes'
you can change the settings from within a connection; either local to the connection, or to take effect globally.
You can retrieve the current mode by issuing a SELECT @@sql_mode
statement.
SET [SESSION|GLOBAL] sql_mode='modes'
you can change the settings.
CREATE [FUNCTION|PROCEDURE]
, ALTER [FUNCTION|PROCEDURE]
, DROP [FUNCTION|PROCEDURE]
, and SHOW CREATE [FUNCTION|PROCEDURE], INFORMATION_SCHEMA
(see http://dev.mysql.com/doc/mysql/en/routines-table.html).
INFORMATION_SCHEMA.ROUTINES
table:
SELECT ROUTINE_TYPE,ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='dbname';
See also http://dev.mysql.com/doc/mysql/en/routines-table.html
SHOW CREATE [FUNCTION|PROCEDURE] procname;
See also http://dev.mysql.com/doc/mysql/en/show-create-procedure.html
SHOW CREATE [FUNCTION|PROCEDURE]
(see http://dev.mysql.com/doc/mysql/en/show-create-procedure.html) or INFORMATION_SCHEMA
(see http://dev.mysql.com/doc/mysql/en/routines-table.html)
UPDATE
) that causes a trigger to fire.
SIGNAL
and RESIGNAL
statements are on the TODO.
HANDLER
definitions according to the SQL standard. See http://dev.mysql.com/doc/mysql/en/declare-handlers.html for details.
SELECT
inside a stored procedure or function, the result set is returned directly to the client. You will need to use the MySQL 4.1 client-server protocol for this to work. This means that, for instance in PHP, you need to use the mysqli extension rather than the old mysql extension.
SELECT
by not opening a cursor on it (it will be sent directly to the client), or by selecting into variables.
SELECT
inside a stored procedure or function, the result set is returned directly to the client. You will need to use the MySQL 4.1 client-server protocol for this to work. This means that, for instance in PHP, you need to use the mysqli extension rather than the old mysql extension.
FOR EACH ROW
, that is, the trigger is activated for each row that is inserted/updated/deleted. We currently don't allow FOR EACH STATEMENT
triggers.
TIMESTAMP
columns, and columns defined as AUTO_INCREMENT
.
CREATE TRIGGER
statement. See http://dev.mysql.com/doc/mysql/en/triggers.html for details. Management features through, for instance, INFORMATION_SCHEMA
are currently being implemented.
INFORMATION_SCHEMA.TRIGGERS
table:
SELECT TRIGGER_NAME,EVENT_MANIPULATION,EVENT_OBJECT_TABLE,ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='dbname';
See also http://dev.mysql.com/doc/mysql/en/triggers-table.html
SHOW TRIGGERS
statement.
See also http://dev.mysql.com/doc/mysql/en/show-create-triggers.html
CREATE TABLE
information. In the near future, the trigger information will be included in the .FRM structure. In a nutshell, triggers belong with a table.
FEDERATED
storage engine (see http://dev.mysql.com/doc/mysql/en/federated-storage-engine.html).
CHECK TABLE
statement.
INSERT
statement has a column list that makes it clear there's only one table involved. You cannot insert into multiple tables with a single statement.
INFORMATION_SCHEMA
.
INFORMATION_SCHEMA
as in the SQL standard.
mysql
extension can work with MySQL 5.0, but does not support the new authentication protocol (MySQL 4.1), prepared statements (MySQL 4.1), cursors, or multiple result sets. Only the new mysqli
extension (PHP 5.0 and up) covers and supports all features of MySQL 4.1 and 5.0.
ISAM
tables (used until MySQL 3.22) was removed in MySQL 5.0. To convert a table from ISAM
to MyISAM
, simply issue a statement like ALTER TABLE tblname ENGINE=MYISAM
.
MyISAM
tables was also removed. This was an old feature used to allow large tables with file systems that did not support large (>2GB) files. All modern file systems allow for larger tables, and there are also other solutions such as MERGE
tables and VIEWs
.
VARCHAR
column type now retains any trailing spaces (all storage engines).
MEMORY
tables (called HEAP
, prior to MySQL version 4.1) can now also contain VARCHAR
columns.
“Of the five databases we tested, only Oracle9i and MySQL were able to run our Nile application as originally written for 8 hours without problems.” —eWeek, “Server Databases Clash”
© 1995-2005 MySQL AB. All rights reserved.