Australia photo & PC memo

Every table has a format file in its database directory. The format file is created by the server and contains the definition, or structure, of the table. The format filename is the save as the table name, plus an .frm suffix.

The MyISAM engine creates a data file (.MYD) and index file (.MYI) for each table.

By default, the InnoDB engine shares files for multiple tables. If Country is an InnoDB table, there will e a Country.frm format file but the InnoDB engine stores the table data and index information in the InnoDB shared table space. (InnoDB table space is shared among all databases.)

The MEMORY engine does not use any disk storage.

The INFORMATION_SCHEMA database has a SCHEMATA table that contains database metadata.

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'searching database';

The output of the SHOW DATABASES statement depends on whether you have the SHOW DATABASES privilege. It can take a LIKE clause.

SHOW DATABASES LIKE 'm%';

SHOW CREATE DATABASE shows the CREATE DATABASE statement.

Each database directory has a default character set and collation. The properties are stored in a file named db.opt in the database directory.
MySQL does not place any limits on the number of databases.
In MySQL, the word "database" is same as "schema". So "SHOW SCHEMAS" instead of "SHOW DATABASES" is doable.

The CREATE/ALTER DATABASE statements have two optional clauses, CHARACTER SET and COLLATE.
Example:
CREATE DATABASE database_name CHARACTER SET utf8 COLLATE utf8_japanese_ci;
ALTER DATABASE (database_name) CHARACTER SET latin1 COLLATE latin1_swedish_ci;

ALTER DATABASE affects only creation of new tables. You cannot use ALTER DATABASE to rename a database.

Any warning generated when IF EXISTS is used can be displayed with SHOW WARNINGS.

By default, a function name and the opening parenthesis that follows it must be written with no intervening space. If the IGNORE_SPACE SQL mode is enabled, the server allows spaces.

Reserved words and function names are not case sensitive.

An identifier cannot consist entirely of digits, if it's not quoted. If the ANSI_QUOTES SQL modes is enabled, double quotes are acceptable to quote an identifier.
Database and table names can't contain '.', '/', or '\'.

For database and table identifiers, case sensitivity depends on the OS and file system of the server host, and on the setting of the lower_case_table_names system variable.
Column, index, stored routine and trigger identifiers are not case sensitive.
Column aliases are not case sensitive.

A table name may be qualified with the name of the database to which it belongs.
For example:

SELECT db1t_a.id, db2t_b.name FROM db1.table_a db1t_a
   INNER JOIN db2.table_b db2t_b
   ON db1t_a.id = db2t_b.id;

Using INSERT IGNORE or UPDATE IGNORE causes MySQL to use non-strict behavior for the statement (producing warnings rather than errors).

Before MySQL 5, date values were required only to have month and day values in the range from 1 to 12 and 1 to 31, respectively. This means that MySQL accepted dates such as '2009-02-31'.
MySQL 5 requires that month and day values correspond to an actual legal date, so '2009-02-31' is not considered a valid date. MySQL 5 converts it to '0000-00-00' and generates a warning (in strict mode, error).
Enabling ALLOW_INVALID_DATES allows you to use the respective ranges of 1 to 12 and 1 to 31.

SET sql_mode = 'STRICT_ALL_TABLES,ALLOW_INVALID_DATES';

Strict mode is enabled by using STRICT_TRANS_TABLES and STRICT_ALL_TABLES.

SET sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE';
SET sql_mode = 'TRADITIONAL';

STRICT_TRANS_TABLES:
If an error occurs for a transactional table, the statement rolls back. For a non-transactional table, the statement could be adjusted by MySQL if possible. (partial updates might occur)

STRICT_ALL_TABLES:
Similar to STRICT_TRANS_TABLES but the statement could be aborted for a non-transactional table. (partial updates might occur)

Additional Input Data Restrictions:
ERROR_FOR_DIVISION_BY_ZERO
* If no strict mode, division by zero results in a value of NULL and a warning, not an error.
NO_ZERO_DATE, NO_ZERO_IN_DATE
* By default, MySQL allows "zero" dates even if you enable strict mode.
TRADITIONAL
* Strict mode plus above additional restrictions

If MySQL is not operating in strict mode, it adjusts invalid input values to legal values when possible and generates warning messages.

For a single-row INSERT which is assigning NULL to a NOT NULL column, an error occurs and the statement fails. However, for a multiple-row INSERT, MySQL assigns the column the implicit default value for its data type.

If a column is changed to NOT NULL using ALTER TABLE, MySQL converts NULL values to the implicit default value for the data type. (Warning but No error!)

For numeric columns:

UNSIGNED

ZEROFILL (causes UNSIGNED)

AUTO_INGREMENT (may be only one per table, and must be indexed, and must be defined as NOT NULL)
LAST_INSERT_ID() returns the most recently generated AUTO_INCREMENT value.
If you update an AUTO_INCREMENT column to NULL or 0, the column is set to 0.
By default, inserting 0 in an AUTO_INCREMENT column has the same effect as inserting NULL unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled.
MyISAM storage engine supports composite indexes. In composite indexes, AUTO_INCREMENT value could be reused.

For string columns:

CHARACTER SET (= CHARSET) ex. utf-8

COLLATE (if CHARSET is omitted, set to the collation's character set.)

BINARY

For general columns:

NOT NULL

DEFAULT value (can't use for TEXT, BLOB and AUTO_INCREMENT (integer) columns)

A default value must be a constant except CURRENT_TIMESTAMP for one TIMESTAMP column.

Implicit default values are:
0 for numeric columns
"" for string columns except ENUM
the first enumeration member for ENUM
zero value for temporal columns

DATE      3 bytes '1000-01-01' to '9999-12-31'
TIME      3 bytes '-838:59:59' to '838:59:59'*
DATETIME  8 bytes '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP 4 bytes '1970-01-01 00:00:00' to mid-year 2037
YEAR      1 byte  1901 to 2155 or (19)70 to (20)69

* TIME columns can be sued to represent elapsed time.

The date format corresponds to the ANSI SQL (ISO 8601) format. This can be changeable by using DATE_FORMAT() and TIME_FORMAT().
TIMESTAMP column has two special attributes, DEFAULT CUURENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, and both are assigned if those are ommitted when the table is created (after version 4.1), also NOT NULL is default.
MySQL does not allow to use DEFAULT CURRENT_TIMESTAMP with one column and ON UPDATE CURRENT_TIMESTAMP with another. This is achievable by creating two TIMESTAMP columns defining one column, that shold hold the creation time, with DEFAULT 0 and explicitly set it to NULL when a new recored is inserted, and another column, that should hold the update time, with DEFAULT CURRENT_TIMESTAMP:

CREATE TABLE test (
   created  TIMESTAMP DEFAULT 0,
   modified TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   aaaaa    VARCHAR(10)
);

INSERT INTO test (created, aaaaa) VALUES (NULL, 'test');
UPDATE test SET aaaaa = 'test 2';

MySQL is possible to set the current time zone on a per-connection basis.
1. Signed hour/minute offset ('+hh:mm' or '-hh:mm')
2. Named time zone ('US/Eastern')
3. SYSTEM time zone

Examples:
SELECT @@global.time_zone, @@session.time_zone;
SET [SESSION] time_zone = '+00:00';
SELECT CONVERT_TZ('2007-08-02 12:00:00', '+01:00', '+03:00');

CHAR       Fixed length up to 255 characters
VARCHAR    Variable length up to approx. 65,535 characters
           (+ 1 byte for a less than 256, 2 bytes otherwise*)
TINYTEXT   Variable length up to 255 characters (+ 1 byte*)
TEXT       Variable length up to 65,535 characters (+ 2 bytes*)
MEDIUMTEXT Variable length up to 16,777,215 chars (+ 3 bytes*)
LONGTEXT   Variable length up to 4,294,967,295 chars (+ 4 bytes*)

* extra storage space for character length

For variable-length (VARCHAR, TEXT) columns, MySQL allocates only the required amount of space. That means if the character is triple-byte, one character use 3 bytes plus a length byte.
Non-binary strings have a character set and a character set might have many collations (string order).
The collation decides if uppercase and lowercase versions and/or accent marks of a given character are equivalent.
A collation can be a binary collation. One effect of this is that for character sets with uppercase and lowercase characters or accented characters, the collation is case sensitive and accent sensitive.

mysql# SHOW COLLATION LIKE 'latin1%';

Each collation name ends with _ci, _cs or _bin, signifying that the collation is Case Insensitive, Case Sensitive or BINary. The binary columns are suitable for raw data such as images or compressed data.

BINARY     Fixed length, binary string
VARBINARY  Variable length, binary string
BLOB       variable length, binary string
           (TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB)

ENUM       A fixed set of legal values up to 65,535
SET        A fixed set of legal values up to 64

MySQL represents ENUMerations as integers internally.
Illigal value is stored as 0 and display as the empty string. (in strict SQL mode, an error occurs when you try to store an invalid value.)
MySQL represents SET as a bitmap using one bit per member.
It's ignored if you try to store an invalid value into a SET column (only valid values are stored)
When you do SELECT, INSERT or UPDATE, you can use either string or internal integer value (it could be confusing though.)