SpirirHost Support Centre: MySQL Reference Manual p.38

<< previous page -- table of contents -- next page >>

Chapter_1

Chapter_2

Chapter_3

Chapter_4

Chapter_5

Return to Table of Contents

Table of Contents
1. General Information
2. MySQL Installation
3. Tutorial Introduction
4. Database Administration
5. MySQL Optimisation
6. MySQL Language Reference
7. MySQL Table Types
8. MySQL APIs
9. Extending MySQL

38 MySQL Technical Reference for Version 4.0.3 In many cases, users have wanted ROLLBACK and/or LOCK TABLES for the purpose of managing unique identiers for some tables. This can be handled much more eciently by using an AUTO_INCREMENT column and either the SQL function LAST_INSERT_ID() or the C API function mysql_insert_id(). See Section 8.4.3.30 [mysql_insert_id()], page 580. You can generally code around row-level locking. Some situations really need it, but they are very few. InnoDB tables support row-level locking. With MyISAM, you can use a ag column in the table and do something like the following: UPDATE tbl_name SET row_flag=1 WHERE id=ID; MySQL returns 1 for the number of aected rows if the row was found and row_flag wasn't already 1 in the original row. You can think of it as though MySQL Server changed the preceding query to: UPDATE tbl_name SET row_flag=1 WHERE id=ID AND row_flag <> 1; 1.7.4.4 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 re-issuing 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 the server. A trigger is a stored procedure that is invoked when a particular event occurs. For ex- ample, 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. Our aim is to have stored procedures implemented in MySQL Server around version 5.0. We are also looking at triggers. 1.7.4.5 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; See Section 6.4.1.1 [JOIN], page 451. See Section 3.5.6 [example-Foreign keys], page 171. In MySQL Server 3.23.44 and up, InnoDB tables support checking of foreign key constraints. See Section 7.5 [InnoDB], page 506. For other table types, MySQL Server does parse the FOREIGN KEY syntax in CREATE TABLE commands, but without further action being taken. 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 Reference Manual

Home
Hosting Plans \| Domain Registration \| About Us \| Contact Us \| Site Map Terms of Use \| Privacy Policy \| Guarantees Merchant Accounts

SpiritHost - web hosting for spiritual and education sites SpiritHit.com - Religious and Spiritual Portal Copyright © 2002 Dyntex Group, Inc. All Rights Reserved

Return to Table of Contents	Back to top

Web Hosting: Manuals & FAQ's

1. Unix-Based Web Hosting
2. Unix Dedicated Servers
3. Windows Dedicated Servers
4. CuteFTP User’s Guide
5. CuteHTML User’s Guide
6. WS_FTP Pro User's Guide
7. Miva Order User's Guide
8. Miva Merchant User's Guide