MySQL - Stored Procedures and Triggers
A stored procedure is a set of SQL commands that can be
compiled and stored in the server. Once this has been
done, clients don't need to keep reissuing the entire
query but can refer to the stored procedure. This
provides better performance because the query has to be
parsed only once, and less information needs to be sent
between the server and the client. You can also raise
the conceptual level by having libraries of functions in
A trigger is a stored procedure that is invoked when a
particular event occurs. For example, you can install a
stored procedure that is triggered each time a record is
deleted from a transaction table and that automatically
deletes the corresponding customer from a customer table
when all his transactions are deleted.
The planned update language will be able to handle
stored procedures, but without triggers. Triggers
usually slow down everything, even queries for which
they are not needed.
MySQL - Foreign Keys
Note that foreign keys in SQL are not used to join
tables, but are used mostly for checking referential
integrity (foreign key constraints). If you want to get
results from multiple tables from a SELECT statement,
you do this by joining tables:
SELECT * from table1,table2 where table1.id = table2.id;
The FOREIGN KEY syntax in MySQL exists only for
compatibility with other SQL vendors' CREATE TABLE
commands; it doesn't do anything. The FOREIGN KEY syntax
without ON DELETE ... is mostly used for documentation
purposes. Some ODBC applications may use this to produce
automatic WHERE clauses, but this is usually easy to
override. FOREIGN KEY is sometimes used as a constraint
check, but this check is unnecessary in practice if rows
are inserted into the tables in the right order. MySQL
only supports these clauses because some applications
require them to exist (regardless of whether or not they
In MySQL, you can work around the problem of ON DELETE
... not being implemented by adding the appropriate
DELETE statement to an application when you delete
records from a table that has a foreign key. In practice
this is as quick (in some cases quicker) and much more
portable than using foreign keys.
In the near future we will extend the FOREIGN KEY
implementation so that at least the information will be
saved in the table specification file and may be
retrieved by mysqldump and ODBC. At a later stage we
will implement the foreign key constraints for
application that can't easily be coded to avoid them.
MySQL - Reasons NOT to Use Foreign Keys constraints
There are so many problems with foreign key constraints
that we don't know where to start:
Foreign key constraints make life very complicated,
because the foreign key definitions must be stored in a
database and implementing them would destroy the whole
``nice approach'' of using files that can be moved,
copied, and removed. The speed impact is terrible for
INSERT and UPDATE statements, and in this case almost
all FOREIGN KEY constraint checks are useless because
you usually insert records in the right tables in the
right order, anyway. There is also a need to hold locks
on many more tables when updating one table, because the
side effects can cascade through the entire database.
It's MUCH faster to delete records from one table first
and subsequently delete them from the other tables.
You can no longer restore a table by doing a full delete
from the table and then restoring all records (from a
new source or from a backup).
If you use foreign key constraints you can't dump and
restore tables unless you do so in a very specific
order. It's very easy to do ``allowed'' circular
definitions that make the tables impossible to re-create
each table with a single create statement, even if the
definition works and is usable.
It's very easy to overlook FOREIGN KEY ... ON DELETE
rules when one codes an application. It's not unusual
that one loses a lot of important information just
because a wrong or misused ON DELETE rule.
The only nice aspect of FOREIGN KEY is that it gives
ODBC and some other client programs the ability to see
how a table is connected and to use this to show
connection diagrams and to help in building applicatons.
MySQL will soon store FOREIGN KEY definitions so that a
client can ask for and receive an answer about how the
original connection was made. The current `.frm' file
format does not have any place for it. At a later stage
we will implement the foreign key constraints for
application that can't easily be coded to avoid them.
MySQL - `--' as the Start of a Comment
MySQL doesn't support views, but this is on the TODO.
MySQL - Views
Some other SQL databases use `--' to start comments.
MySQL has `#' as the start comment character, even if
the mysql command-line tool removes all lines that start
with `--'. You can also use the C comment style /* this
is a comment */ with MySQL.
MySQL Version 3.23.3 and above supports the `--' comment
style only if the comment is followed by a space. This
is because this degenerate comment style has caused many
problems with automatically generated SQL queries that
have used something like the following code, where we
automatically insert the value of the payment for
UPDATE tbl_name SET credit=credit-!payment!
What do you think will happen when the value of payment
Because 1--1 is legal in SQL, we think it is terrible
that `--' means start comment.
In MySQL Version 3.23 you can, however, use: 1-- This is
The following discussion only concerns you if you are
running a MySQL version earlier than Version 3.23:
If you have a SQL program in a text file that contains
`--' comments you should use:
shell> replace " --" " #" < text-file-with-funny-comments.sql
| mysql database
instead of the usual:
shell> mysql database < text-file-with-funny-comments.sql
You can also edit the command file ``in place'' to
change the `--' comments to `#' comments:
shell> replace " --" " #" -- text-file-with-funny-comments.sql
Change them back with this command:
shell> replace " #" " --" -- text-file-with-funny-comments.sql
Page Numbers : 1