Archives

You are currently viewing archive for November 2007
Category: Study
Posted by: hajime osako
DELETE returns a true row count, but TRUNCATE TABLE returns 0.
TRUNCATE TABLE and DELETE without WHERE clause may reset the sequence number of AUTO_INCREMENT.
You can use ORDER BY and LIMIT clauses in DELETE statements.

TRUNCATE TABLE requires the DELETE privilege.
Category: Study
Posted by: hajime osako
The UPDATE statement does not have any effect if the statement does not actually change any column values, even there is a TIMESTAMP column with ON UPDATE CURRENT_TIMESTAMP.

You can use ORDER BY and LIMIT in the UPDATE statement.

The --safe-updates option is for preventing dangerous UPDATE statement, such as no WHERE clause.
Category: Study
Posted by: hajime osako
If both the column list and the VALUES list are empty, MySQL creates a new record with each column set to its default:

 INSERT INTO table_name () VALUES();

When you have many records to add, multiple-row statements provide better performance (but may reach the max_allowed_packet size.)

In error condition for multiple record INSERT statements, MyISAM inserts the records until the error record, but for InnoDB, the entire statement fails and no records are inserted.

INSERT ... ON DUPLICATE KEY UPDATE clause allows you to do in one statement what otherwise requires two (INSERT and UPDATE).

 INSERT INTO log_table (id, date_time, counter)
 VALUES (1, NOW(), 1)
 ON DUPLICATE KEY UPDATE counter=counter+1;

Note: If an already existing record is updated, the "rows affected" value is 2.

If a table contains multiple unique-valued indexes, REPLACE replaces each of those records (deletes the matching multiple rows, then inserts one row.)

REPLACE requires the INSERT and DELETE privilege.
Category: Study
Posted by: hajime osako
IN() always returns NULL when used to test NULL, even if NULL is included in the list.

IF() and CASE as used in expressions have somewhat different syntax than the IF and CASE statements such can be used within compound statements.

For approximate values, ROUND() uses the rounding method provided in the C library.

 SELECT ROUND(11.5), ROUND(-11.5); <--- returns 12 and -12
 SELECT ROUND(1.15E1), ROUND(-1.15E1); <--- returns 11 and -11

FLOOR() returns the largest integer not greater than its argument.

 SELECT FLOOR(14.7), FLLOR(-14.7); <--- returns 14 and -15

The LENGTH() and CHAR_LENGTH() functions determine string lengths in byte and character units, respectively.

The CONCAT() returns NULL if any of its arguments are NULL, however CONCAT_WS() ignores NULL values.

The STRCMP() function compares two strings and returns -1, 0, or 1.

MySQL encription functions:

 PASSWORD() (one-way)
 ENCODE() and DECODE()
 DES_ENCRYPTY() and DES_DECRYPT()
 AES_ENCRYPT() and AES_DECRYPT()

MAKEDATE() takes two arguments year and day of the year.
MAKETIME() produces a time from hour, minute, and secound arguments.

To determine the current date or time, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, or NOW().

MySQL disallows a double dash (--) without a space as comment.
/*! */ is not a comment in MySQL.

 CREATE TABLE t (i INT) /*! ENGINE = MEMORY */
 SHOW /*!50002 FULL */ TABLES; <-- If MySQL is 5.0.2 or newer.
Category: Study
Posted by: hajime osako
Temporal Expressions

To perform interval arithmetic, use the INTERVAL keyword and a unit value:

SELECT '2007-12-31' + INTERVAL 10 DAY; (or INTERVAL 10 DAY + '2007-12-31')
SELECT '2007-12-31' - INTERVAL 10 DAY;

* Not DAYS.

The INTERVAL can be specified in units such as SECOND, MINUTE, HOUR, DAY, MONTH or YEAR.


NULL Values

Use of NULL values in arithmetic or comparison operations normally produces NULL results. Even comparing NULL to itself results in NULL.
The following statements return NULL:

SELECT NULL + 1, NULL < 1;
SELECT NULL = 1, NULL != NULL;
SELECT NULL LIKE '%', NULL LIKE NULL;

In MySQL, <=> operator works like = and this works with NULL operand.
NULL values sort together (ORDER BY), group together (GROUP BY), and are not distinct.
Expressions that cannot be evaluated (such as 1/0) produce NULL as a result, but the result NULL value cannot be inserted. This is controlled by ERROR_FOR_DIVISION_BY_ZERO mode.