MYSQL(CLASS 5)

Basic MySQL Commands
· ASIT KUMAR MAHAPATRA.
· SOFTWARE ENGINEER TRAINEE
· BAY AREA INFOTECH
· CREATE TABLE syntax
· Silent column specification changes
· DROP TABLE syntax
· DELETE syntax
· SELECT syntax
· JOIN syntax
· INSERT syntax
· REPLACE syntax
· UPDATE syntax

CREATE TABLE syntax
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]

create_definition:
col_name type [NOT NULL NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] (index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name] (index_col_name,...)
or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
or CHECK (expr)

type:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
or SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
or INT[(length)] [UNSIGNED] [ZEROFILL]
or INTEGER[(length)] [UNSIGNED] [ZEROFILL]
or BIGINT[(length)] [UNSIGNED] [ZEROFILL]
or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
or NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
or CHAR(length) [BINARY]
or VARCHAR(length) [BINARY]
or DATE
or TIME
or TIMESTAMP
or DATETIME
or TINYBLOB
or BLOB
or MEDIUMBLOB
or LONGBLOB
or TINYTEXT
or TEXT
or MEDIUMTEXT
or LONGTEXT
or ENUM(value1,value2,value3,...)
or SET(value1,value2,value3,...)

index_col_name:
col_name [(length)]

reference_definition:
REFERENCES tbl_name [(index_col_name,...)]
[MATCH FULL MATCH PARTIAL]
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference_option:
RESTRICT CASCADE SET NULL NO ACTION SET DEFAULT

table_options:
TYPE = {ISAM MYISAM HEAP}
or AUTO_INCREMENT = #
or AVG_ROW_LENGTH = #
or CHECKSUM = {0 1}
or COMMENT = "string"
or MAX_ROWS = #
or MIN_ROWS = #
or PACK_KEYS = {0 1}
or PASSWORD = "string"
or DELAY_KEY_WRITE = {0 1}
or ROW_FORMAT= { default dynamic static compressed }

select_statement:
[IGNORE REPLACE] SELECT ... (Some legal select statement)
CREATE TABLE creates a table with the given name in the current database. Rules for allowable table names are given in Database, table, index, column and alias names. An error occurs if there is no current database or if the table already exists. The table name can be specified as db_name.tbl_name. This works whether or not there is a current database.
You can use the TEMPORARY keyword when you create a table. A temporary table will automatically be deleted if a connection dies and the name is per connection. This means that two different connections can both use the same temporary table name without conflicting with each other or with an existing table of the same name. (The existing table is hidden until the temporary table is deleted).
You can use the keywords IF NOT EXISTS so that an error does not occur if the table already exists. Note that there is no verification that the table structures are identical.
Each table tbl_name is represented by some files in the database directory. In the case of MyISAM-type tables you will get:
File
Purpose
tbl_name.frm
Table definition (form) file
tbl_name.MYD
Data file
tbl_name.MYI
Index file
For more information on the properties of the various column types, see section Column types.
· If neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified.
· An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value will be reused. If you delete all rows in the table, the sequence starts over. Note: There can be only one AUTO_INCREMENT column per table, and it must be indexed. To make MySQL compatible with some ODBC applications, you can find the last inserted row with the following query:
SELECT * FROM tbl_name WHERE auto_col IS NULL
· NULL values are handled differently for TIMESTAMP columns than for other column types. You cannot store a literal NULL in a TIMESTAMP column; setting the column to NULL sets it to the current date and time. Because TIMESTAMP columns behave this way, the NULL and NOT NULL attributes do not apply in the normal way and are ignored if you specify them. On the other hand, to make it easier for MySQL clients to use TIMESTAMP columns, the server reports that such columns may be assigned NULL values (which is true), even though TIMESTAMP never actually will contain a NULL value. You can see this when you use DESCRIBE tbl_name to get a description of your table. Note that setting a TIMESTAMP column to 0 is not the same as setting it to NULL, because 0 is a valid TIMESTAMP value.
· If no DEFAULT value is specified for a column, MySQL automatically assigns one. If the column may take NULL as a value, the default value is NULL. If the column is declared as NOT NULL, the default value depends on the column type:
· For numeric types other than those declared with the AUTO_INCREMENT attribute, the default is 0. For an AUTO_INCREMENT column, the default value is the next value in the sequence.
· For date and time types other than TIMESTAMP, the default is the appropriate ``zero'' value for the type. For the first TIMESTAMP column in a table, the default value is the current date and time. See Date and time types.
· For string types other than ENUM, the default is the empty string. For ENUM, the default is the first enumeration value.
· KEY is a synonym for INDEX.
· In MySQL, a UNIQUE key can have only distinct values. An error occurs if you try to add a new row with a key that matches an existing row.
· A PRIMARY KEY is an unique KEY with the extra constraint that all key columns must be defined as NOT NULL. In MySQL the key is named PRIMARY. A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and some applications ask for the PRIMARY KEY in your tables, MySQL will return the first UNIQUE key, which doesn't have any NULL columns, as the PRIMARY KEY.
· A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attibute in a column specification. Doing so will mark only that single column as primary. You must use the PRIMARY KEY(index_col_name, ...) syntax.
· If you don't assign a name to an index, the index will be assigned the same name as the first index_col_name, with an optional suffix (_2, _3, ...) to make it unique. You can see index names for a table using SHOW INDEX FROM tbl_name. See SHOW syntax.
· Only the MyISAM table type supports indexes on columns that can have NULL values. In other cases you must declare such columns NOT NULL or an error results.
· With col_name(length) syntax, you can specify an index which uses only a part of a CHAR or VARCHAR column. This can make the index file much smaller. See Column indexes.
· Only the MyISAM table type supports indexing on BLOB and TEXT columns. When putting an index on a BLOB or TEXT column you MUST always specify the length of the index:
CREATE TABLE test (blob_col BLOB, index(blob_col(10)));
· When you use ORDER BY or GROUP BY with a TEXT or BLOB column, only the first max_sort_length bytes are used. See The BLOB and TEXT types.
· The FOREIGN KEY, CHECK and REFERENCES clauses don't actually do anything. The syntax for them is provided only for compatibility, to make it easier to port code from other SQL servers and to run applications that create tables with references.
· Each NULL column takes one bit extra, rounded up to the nearest byte.
· The maximum record length in bytes can be calculated as follows:
row length = 1
+ (sum of column lengths)
+ (number of NULL columns + 7)/8
+ (number of variable-length columns)
· The different table types are:
ISAM
The original table handler
MyISAM
The new binary portable table handler
HEAP
The data for this table is only stored in memory
The other table options are used to optimize the behavior of the table. In most cases, you don't have to specify any of them. The options work for all table types, if not otherwise indicated.
AUTO_INCREMENT
The next auto_increment value you want to set for your table (MyISAM)
AVG_ROW_LENGTH
An approximation of the average row length for your table. You only need to set this for tables with variable size records.
CHECKSUM
Set this to 1 if you want MySQL to maintain a checksum for all rows (makes the table a little slower to update but makes it easier to find corrupted tables) (MyISAM)
COMMENT
A 60 character comment for your table
MAX_ROWS
Max number of rows you plan to store in the table
MIN_ROWS
Minimum number of rows you plan to store in the table
PACK_KEYS
Set this to 1 if you want to have smaller index. This usually makes updates slower and reads faster (MyISAM, ISAM).
PASSWORD
Encrypt the .frm file with a password. This option doesn't do anything in the standard MySQL version.
DELAY_KEY_WRITE
Set this to 1 if want to delay key table updates until the table is closed (MyISAM).
ROW_FORMAT
Defines how the rows should be stored (for the future).
When you use a MyISAM table, MySQL uses the product of max_rows * avg_row_length to decide how big the resulting table will be. If you don't specify any of the above options, the maximum size for a table will be 4G (or 2G if your operating systems only supports 2G tables). If you don't use PACK_KEYS, the default is to only pack strings, not numbers. If you use PACK_KEYS=1, numbers will be packed as well. When packing binary number keys, MySQL will use prefix compression. This means that you will only get a big benefit of this if you have many numbers that are the same. Prefix compression means that every key needs one extra byte to indicate how many bytes of the previous key are the same for the next key (note that the pointer to the row is stored in high-byte-first-order directly after the key, to improve compression. This means that if you have many equal keys on two rows in a row, all following 'same' keys will usually only take 2 bytes (including the pointer to the row). Compare this to the ordinary case where the following keys will take 'storage_size_for_key' + pointer_size (usually 4). On the other hand, if all keys are totally different, you will lose 1 byte per key, if the key isn't a key that can have NULL values (In this case the packed key length will be stored in the same byte that is used to mark if a key is NULL).
· If you specify a SELECT after the CREATE STATEMENT, MySQL will create new fields for all elements in the SELECT. For example:
CREATE TABLE test (a int not null auto_increment,
primary key (a), key(b))
TYPE=HEAP SELECT b,c from test2;
This will create a HEAP table with 3 columns. Note that the table will automatically be deleted if any errors occur while copying data into the table.

Silent column specification changes
In some cases, MySQL silently changes a column specification from that given in a CREATE TABLE statement. (This may also occur with ALTER TABLE.)
· VARCHAR columns with a length less than four are changed to CHAR.
· If any column in a table has a variable length, the entire row is variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columnss. This doesn't affect how you use the columns in any way; in MySQL, VARCHAR is just a different way to store characters. MySQL performs this conversion because it saves space and makes table operations faster.
· TIMESTAMP display sizes must be even and in the range from 2 to 14. If you specify a display size of 0 or greater than 14, the size is coerced to 14. Odd-valued sizes in the range from 1 to 13 are coerced to the next higher even number.
· You cannot store a literal NULL in a TIMESTAMP column; setting it to NULL sets it to the current date and time. Because TIMESTAMP columns behave this way, the NULL and NOT NULL attributes do not apply in the normal way and are ignored if you specify them. DESCRIBE tbl_name always reports that a TIMESTAMP column may be assigned NULL values.
· MySQL maps certain column types used by other SQL database vendors to MySQL types. See Using column types from other database engines.
If you want to see whether or not MySQL used a column type other than the one you specified, issue a DESCRIBE tbl_name statement after creating or altering your table.
DROP TABLE syntax
DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...]
DROP TABLE removes one or more tables. All table data and the table definition are removed, so be careful with this command!
You can use the keywords IF EXISTS to prevent an error from occurring for tables that don't exist.
DELETE syntax
DELETE [LOW_PRIORITY] FROM tbl_name
[WHERE where_definition] [LIMIT rows]
DELETE deletes rows from tbl_name that satisfy the condition given by where_definition, and returns the number of records deleted.
If you issue a DELETE with no WHERE clause, all rows are deleted. MySQL does this by recreating the table as an empty table, which is much faster than deleting each row. In this case, DELETE returns zero as the number of affected records. (MySQL can't return the number of rows that were actually deleted, since the recreate is done without opening the data files. As long as the table definition file `tbl_name.frm' is valid, the table can be recreated this way, even if the data or index files have become corrupted.).
If you really want to know how many records are deleted when you are deleting all rows, and are willing to suffer a speed penalty, you can use a DELETE statement of this form:
DELETE FROM tbl_name WHERE 1>0;
Note that this is MUCH slower than DELETE FROM tbl_name with no WHERE clause, because it deletes rows one at a time.
If you specify the keyword LOW_PRIORITY, execution of the DELETE is delayed until no other clients are reading from the table.
Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. To reclaim unused space and reduce file sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganize tables. OPTIMIZE TABLE is easier, but myisamchk is faster. See OPTIMIZE TABLE syntax.
The MySQL-specific LIMIT rows option to DELETE tells the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a specific DELETE command doesn't take too much time. You can simply repeat the DELETE command until the number of affected rows is less than the LIMIT value.
SELECT syntax
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY]
[DISTINCT DISTINCTROW ALL]
select_expression,...
[INTO {OUTFILE DUMPFILE} 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY col_name,...]
[HAVING where_definition]
[ORDER BY {unsigned_integer col_name formula} [ASC DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name] ]
SELECT is used to retrieve rows selected from one or more tables. select_expression indicates the columns you want to retrieve. SELECT may also be used to retrieve rows computed without reference to any table. For example:
SELECT 1 + 1;
-> 2
All keywords used must be given in exactly the order shown above. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause.
· A SELECT expression may be given an alias using AS. The alias is used as the expression's column name and can be used with ORDER BY or HAVING clauses. For example:
mysql> select concat(last_name,', ',first_name) AS full_name
from mytable ORDER BY full_name;
· The FROM table_references clause indicates the tables from which to retrieve rows. If you name more than one table, you are performing a join. For information on join syntax, see JOIN syntax.
· You can refer to a column as col_name, tbl_name.col_name or db_name.tbl_name.col_name. You need not specify a tbl_name or db_name.tbl_name prefix for a column reference in a SELECT statement unless the reference would be ambiguous. See Database, table, index, column and alias names for examples of ambiguity that require the more explicit column reference forms.
· A table reference may be aliased using tbl_name [AS] alias_name.
select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;
select t1.name, t2.salary from employee t1, info t2 where t1.name = t2.name;
· Columns selected for output may be referred to in ORDER BY and GROUP BY clauses using column names, column aliases or column positions. Column positions begin with 1.
select college, region, seed from tournament ORDER BY region, seed;
select college, region AS r, seed AS s from tournament ORDER BY r, s;
select college, region, seed from tournament ORDER BY 2, 3;
To sort in reverse order, add the DESC (descending) keyword to the name of the column in the ORDER BY clause that you are sorting by. The default is ascending order; this may be specified explicitly using the ASC keyword.
· The HAVING clause can refer to any column or alias named in the select_expression. It is applied last, just before items are sent to the client, with no optimization. Don't use HAVING for items that should be in the WHERE clause. For example, do not write this:
mysql> select col_name from tbl_name HAVING col_name > 0;
Write this instead:
mysql> select col_name from tbl_name WHERE col_name > 0;
You can also write queries like this:
mysql> select user,max(salary) from users
group by user HAVING max(salary)>10;
· SQL_SMALL_RESULT, SQL_BIG_RESULT, STRAIGHT_JOIN and HIGH_PRIORITY are MySQL extensions to ANSI SQL92.
· STRAIGHT_JOIN forces the optimizer to join the tables in the order in which they are listed in the FROM clause. You can use this to speed up a query if the optimizer joins the tables in non-optimal order. See EXPLAIN syntax (Get information about a SELECT).
· SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set will be small. In this case, MySQL will use fast temporary tables to store the resulting table instead of using sorting. SQL_SMALL_RESULT is a MySQL
· SQL_BIG_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set will have many rows. In this case, MySQL will directly use disk based temporary tables if needed. MySQL in this case will prefer to do a sort instead doing a temporary table with a key on the GROUP BY elements.
· HIGH_PRIORITY will give the SELECT higher priority than a statement that updates a table. You should only use this for queries that are very fast and must be done at once. A SELECT HIGH_PRIORITY query will run if the table is locked for read even if there is an update statement that is waiting for the table to be free.
· The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments. If two arguments are given, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).
mysql> select * from table LIMIT 5,10; # Retrieve rows 6-15
If one argument is given, it indicates the maximum number of rows to return.
mysql> select * from table LIMIT 5; # Retrieve first 5 rows
In other words, LIMIT n is equivalent to LIMIT 0,n.
· The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, and cannot already exist (among other things, this prevents database tables and files such as `/etc/passwd' from being destroyed). You must have the file privilege on the server host to use this form of SELECT. SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE; the syntax for the export_options part of the statement consists of the same FIELDS and LINES clauses that are used with the LOAD DATA INFILE statement. See LOAD DATA INFILE syntax. In the resulting text file, only the following characters are escaped by the ESCAPED BY character:
· The ESCAPED BY character
· The first character in FIELDS TERMINATED BY
· The first character in LINES TERMINATED BY
Additionally, ASCII 0 is converted to ESCAPED BY followed by 0 (ASCII 48). The reason for the above is that you MUST escape any FIELDS TERMINATED BY, ESCAPED BY or LINES TERMINATED BY characters to reliably be able to read the file back. ASCII 0 is escaped to make it easier to view with some pagers. As the resulting file doesn't have to conform to the SQL syntax, nothing else need be escaped.
If you use INTO DUMPFILE instead of INTO OUTFILE MySQL will only write one row into the file, without any column or line terminations and without any escaping. This is useful if you want to store a blob in a file.
JOIN syntax
MySQL supports the following JOIN syntaxes for use in SELECT statements:
table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference INNER JOIN table_reference
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference ON conditional_expr
table_reference LEFT [OUTER] JOIN table_reference USING (column_list)
table_reference NATURAL LEFT [OUTER] JOIN table_reference
{ oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
The last LEFT OUTER JOIN syntax shown above exists only for compatibility with ODBC.
· A table reference may be aliased using tbl_name AS alias_name or tbl_name alias_name.
select t1.name, t2.salary from employee AS t1, info AS t2
where t1.name = t2.name;
· INNER JOIN and , (comma) are semantically equivalent. Both do a full join between the tables used. Normally, you specify how the tables should be linked in the WHERE condition.
· The ON conditional is any conditional of the form that may be used in a WHERE clause.
· If there is no matching record for the right table in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:
select table1.* from table1
LEFT JOIN table2 ON table1.id=table2.id
where table2.id is NULL;
This example finds all rows in table1 with an id value that is not present in table2 (i.e., all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL, of course.
· The USING (column_list) clause names a list of columns that must exist in both tables. A USING clause such as:
A LEFT JOIN B USING (C1,C2,C3,...)
is defined to be semantically identical to an ON expression like this:
A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,...
· The NATURAL LEFT JOIN of two tables is defined to be semantically equivalent to a LEFT JOIN with a USING clause that names all columns that exist in both tables.
· STRAIGHT_JOIN is identical to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases where the join optimizer puts the tables in the wrong order.
Some examples:
select * from table1,table2 where table1.id=table2.id;
select * from table1 LEFT JOIN table2 ON table1.id=table2.id;
select * from table1 LEFT JOIN table2 USING (id);
select * from table1 LEFT JOIN table2 ON table1.id=table2.id
LEFT JOIN table3 ON table2.id=table3.id;

INSERT syntax
INSERT [LOW_PRIORITY DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES (expression,...),(...),...
or INSERT [LOW_PRIORITY DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or INSERT [LOW_PRIORITY DELAYED] [IGNORE]
[INTO] tbl_name
SET col_name=expression, col_name=expression, ...
INSERT inserts new rows into an existing table. The INSERT ... VALUES form of the statement inserts rows based on explicitly-specified values. The INSERT ... SELECT form inserts rows selected from another table or tables.
tbl_name is the table into which rows should be inserted. The column name list or the SET clause indicates which columns the statement specifies values for.
· If you specify no column list for INSERT ... VALUES or INSERT ... SELECT, values for all columns must be provided in the VALUES() list or by the SELECT. If you don't know the order of the columns in the table, use DESCRIBE tbl_name to find out.
· Any column not explicitly given a value is set to its default value. For example, if you specify a column list that doesn't name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in CREATE TABLE syntax.
· An expression may refer to any column that was set earlier in a value list. For example, you can say this:
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
But not this:
INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);
· If you specify the keyword LOW_PRIORITY, execution of the INSERT is delayed until no other clients are reading from the table. In this case the client has to wait until the insert statement is completed, which may take a long time if the table is in heavy use. This is in contrast to INSERT DELAYED which lets the client continue at once.
· If you specify the keyword IGNORE in an INSERT with many value rows, any rows which duplicate an existing PRIMARY or UNIQUE key in the table are ignored and are not inserted. If you do not specify IGNORE, the insert is aborted if there is any row that duplicates an existing key value. You can check with the C API function mysql_info() how many rows were inserted into the table.
· If MySQL was configured using the DONT_USE_DEFAULT_FIELDS option, INSERT statements generate an error unless you explicitly specify values for all columns that require a non-NULL value.
· The following conditions hold for a INSERT INTO ... SELECT statement:
· The query cannot contain an ORDER BY clause.
· The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query, because it's forbidden in ANSI SQL to SELECT from the same table into which you are INSERTing. (The problem is that the SELECT possibly would find records that were inserted earlier during the same run. When using sub-select clauses, the situation could easily be very confusing!)
· AUTO_INCREMENT columns work as usual.
If you use INSERT ... SELECT or a INSERT ... VALUES statement with multiple value lists, you can use the C API function mysql_info() to get information about the query. The format of the information string is shown below:
Records: 100 Duplicates: 0 Warnings: 0
Duplicates indicates the number of rows that couldn't be inserted because they would duplicate some existing unique index value. Warnings indicates the number of attempts to insert column values that were problematic in some way. Warnings can occur under any of the following conditions:
· Inserting NULL into a column that has been declared NOT NULL. The column is set to its default value.
· Setting a numeric column to a value that lies outside the column's range. The value is clipped to the appropriate endpoint of the range.
· Setting a numeric column to a value such as '10.34 a'. The trailing garbage is stripped and the remaining numeric part is inserted. If the value doesn't make sense as a number at all, the column is set to 0.
· Inserting a string into a CHAR, VARCHAR, TEXT or BLOB column that exceeds the column's maximum length. The value is truncated to the column's maximum length.
· Inserting a value into a date or time column that is illegal for the column type. The column is set to the appropriate ``zero'' value for the type.
The DELAYED option for the INSERT statement is a MySQL-specific option that is very useful if you have clients that can't wait for the INSERT to complete. This is a common problem when you use MySQL for logging and you also periodically run SELECT statements that take a long time to complete. DELAYED is a MySQL extension to ANSI SQL92.
When you use INSERT DELAYED, the client will get an ok at once and the row will be inserted when the table is not in use by any other thread.
Another major benefit of using INSERT DELAYED is that inserts from many clients are bundled together and written in one block. This is much faster than doing many separate inserts.
The following describes in detail what happens when you use the DELAYED option to INSERT or REPLACE. In this description, the ``thread'' is the thread that received an INSERT DELAYED command and ``handler'' is the thread that handles all INSERT DELAYED statements for a particular table.
· When a thread executes a DELAYED statement for a table, a handler thread is created to process all DELAYED statements for the table, if no such handler already exists.
· The thread checks whether or not the handler has acquired a DELAYED lock already; if not, it tells the handler thread to do so. The DELAYED lock can be obtained even if other threads have a READ or WRITE lock on the table. However, the handler will wait for all ALTER TABLE locks or FLUSH TABLES to ensure that the table structure is up to date.
· The thread executes the INSERT statement but instead of writing the row to the table it puts a copy of the final row into a queue that is managed by the handler thread. Any syntax errors are noticed by the thread and reported to the client program.
· The client can't report the number of duplicates or the AUTO_INCREMENT value for the resulting row; it can't obtain them from the server, because the INSERT returns before the insert operation has been completed. If you use the C API, the mysql_info() function doesn't return anything meaningful, for the same reason.
· The update log is updated by the handler thread when the row is inserted into the table. In case of multiple-row inserts, the update log is updated when the first row is inserted.
· After every delayed_insert_limit rows are written, the handler checks whether or not any SELECT statements are still pending. If so, it allows these to execute before continuing.
· When the handler has no more rows in its queue, the table is unlocked. If no new INSERT DELAYED commands are received within delayed_insert_timeout seconds, the handler terminates.
· If more than delayed_queue_size rows are pending already in a specific handler queue, the thread waits until there is room in the queue. This is useful to ensure that the mysqld server doesn't use all memory for the delayed memory queue.
· The handler thread will show up in the MySQL process list with delayed_insert in the Command column. It will be killed if you execute a FLUSH TABLES command or kill it with KILL thread_id. However, it will first store all queued rows into the table before exiting. During this time it will not accept any new INSERT commands from another thread. If you execute an INSERT DELAYED command after this, a new handler thread will be created.
· Note that the above means that INSERT DELAYED commands have higher priority than normal INSERT commands if there is an INSERT DELAYED handler already running! Other update commands will have to wait until the INSERT DELAY queue is empty, someone kills the handler thread (with KILL thread_id) or someone executes FLUSH TABLES.
· The following status variables provide information about INSERT DELAYED commands:
Delayed_insert_threads
Number of handler threads
Delayed_writes
Number of rows written with INSERT DELAYED
Not_flushed_delayed_rows
Number of rows waiting to be written
You can view these variables by issuing a SHOW STATUS statement or by executing a mysqladmin extended-status command.
Note that INSERT DELAYED is slower than a normal INSERT if the table is not in use. There is also the additional overhead for the server to handle a separate thread for each table on which you use INSERT DELAYED. This means that you should only use INSERT DELAYED when you are really sure you need it!
REPLACE syntax
REPLACE [LOW_PRIORITY DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES (expression,...)
or REPLACE [LOW_PRIORITY DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or REPLACE [LOW_PRIORITY DELAYED]
[INTO] tbl_name
SET col_name=expression, col_name=expression,...
REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a unique index, the old record is deleted before the new record is inserted. See INSERT syntax.
UPDATE syntax
UPDATE [LOW_PRIORITY] tbl_name SET col_name1=expr1,col_name2=expr2,...
[WHERE where_definition] [LIMIT #]
UPDATE updates columns in existing table rows with new values. The SET clause indicates which columns to modify and the values they should be given. The WHERE clause, if given, specifies which rows should be updated. Otherwise all rows are updated.
If you specify the keyword LOW_PRIORITY, execution of the UPDATE is delayed until no other clients are reading from the table.
If you access a column from tbl_name in an expression, UPDATE uses the current value of the column. For example, the following statement sets the age column to one more than its current value:
UPDATE persondata SET age=age+1;
UPDATE assignments are evaluated from left to right. For example, the following statement doubles the age column, then increments it:
UPDATE persondata SET age=age*2, age=age+1;
If you set a column to the value it currently has, MySQL notices this and doesn't update it.
UPDATE returns the number of rows that were actually changed.