MySQL : new key feature note

Feature หลักๆ ของ MySQL 5.0 ขอ note ไว้ก่อนดีกว่า...

Stored Procedures

CODE

CREATE PROCEDURE procedure1                /* name */
(IN parameter1 INTEGER)                    /* parameters */
BEGIN                                      /* start of block */
 DECLARE variable1 CHAR(10);                /* variables */
 IF parameter1 = 17 THEN                    /* start of IF */
   SET variable1 = 'birds';                   /* assignment */
 ELSE
   SET variable1 = 'beasts';                  /* assignment */
 END IF;                                   /* end of IF */
 INSERT INTO table1 VALUES (variable1);    /* statement */
END                                       /* end of block */

Chapter 17. Stored Procedures and Functions


Triggers

CODE

mysql> DROP FUNCTION f;
Query OK, 0 rows affected (0.00 sec)

CODE

mysql> CREATE PROCEDURE p ()
   -> BEGIN
   ->   /* This procedure does nothing */
   -> END;//
Query OK, 0 rows affected (0.00 sec)

CODE

 ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE
 DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL
 LOCK OPTIMIZE REPAIR REPLACE REVOKE
 ROLLBACK SAVEPOINT 'SELECT FROM table'
 'SET system variable' 'SET TRANSACTION'
 SHOW 'START TRANSACTION' TRUNCATE UPDATE

Chapter 18. Triggers


Views

CODE

mysql> DROP VIEW v CASCADE;
Query OK, 0 rows affected (0.00 sec)

CODE

mysql> CREATE VIEW v AS
   -> SELECT column1 AS c /* view col name is c */
   -> FROM table1;
Query OK, 0 rows affected (0.01 sec)

CODE

CREATE VIEW v AS SELECT column1 FROM t;
mysql> INSERT INTO v VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM v;
+---------+
| column1 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

Chapter 19. Views

Information Schema

CODE

mysql> CREATE TABLE table1 (column1 INT);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW v AS
   -> SELECT column1 AS c /* view col name is c */
   -> FROM table1;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT table_name, table_type, engine
   -> FROM INFORMATION_SCHEMA.tables
   -> WHERE table_schema = 'tp'
   -> ORDER BY table_type ASC, table_name DESC;
+------------+------------+--------+
| table_name | table_type | engine |
+------------+------------+--------+
| t2         | BASE TABLE | MyISAM |
| t1         | BASE TABLE | InnoDB |
| v1         | VIEW       | NULL   |
+------------+------------+--------+

Chapter 20. The INFORMATION_SCHEMA Database


Archive Storage Engine

CODE

create table test_myisam engine=myisam as select * from client_transaction_hist;
Query OK, 112050 rows affected (1.06 sec)
Records: 112050  Duplicates: 0  Warnings: 0

mysql> create table test_innodb engine=innodb as select * from client_transaction_hist;
Query OK, 112050 rows affected (3.72 sec)
Records: 112050  Duplicates: 0  Warnings: 0

mysql> create table test_archive engine=archive as select * from client_transaction_hist;
Query OK, 112050 rows affected (1.92 sec)
Records: 112050  Duplicates: 0  Warnings: 0

mysql> SELECT  table_name table_name,
   ->         engine,
   ->         ROUND(data_length/1024/1024,2) total_size_mb,
   ->         table_rows
   -> FROM   information_schema.tables
   -> WHERE  table_schema = 'gim' and
   ->        table_name like 'test%'
   -> ORDER BY 3;
+--------------+---------+---------------+------------+
| table_name   | engine  | total_size_mb | table_rows |
+--------------+---------+---------------+------------+
| test_archive | ARCHIVE |          1.64 |     112050 |
| test_myisam  | MyISAM  |          6.46 |     112050 |
| test_innodb  | InnoDB  |          9.52 |     112050 |
+--------------+---------+---------------+------------+

14.8. The ARCHIVE Storage Engine