Technical Details - MySQL References by VisiBone

• Definitions Online: (the ones not defined in print)

  condition◊
  distributed-transaction-commands◊
  encryption◊
  error_number◊
  error_state◊
  h-before◊
  h-after◊
  named-characters◊
  partition-definition◊
  partition-command◊
  reserved-words◊
  time◊

• Extended Features (clusters, partitions)
• Type Synonyms for Columns (below)
• Errata (below)
• Revision History (below)
• Unit Tests:
  • MySQL Unit Tests, version 5, for Windows
  • MySQL Unit Tests, version 4, for Linux

 
 
Type Synonyms for Columns

These column types exist primarily for the comfort of converts from other SQL dialects.

Exotic Column Type		Familiar Equivalent
FLOAT 4		FLOAT
FLOAT 8		DOUBLE
INT1		TINYINT
INT2		SMALLINT
INT3		MEDIUMINT
INT4		INT
INT8		BIGINT
MIDDLEINT		MEDIUMINT
VARCHARACTER		VARCHAR
CHAR VARYING		VARCHAR

 
Errata for the March 2007 Edition

Page 1: Statements A-G

• GRANT's WITH-clause is optional
• CREATE FUNCTION/PROCEDURE's DECLARE clause, HANDLER variant: h-after is comma-separated, not space-separated
• event-definition: time◊ is not defined in print. See Online Definitions. Also the AT-clause should specify time◊ just as the STARTS and ENDS clauses

Page 2: Statements H-Z

• INSERT ... SET c = z, c = z, c = z;  — no parentheses around the assignments! (Thanks to Hacky B.)
• SELECT can have "FROM DUAL" meaning the same as omitting the FROM-clause
• SELECT's HAVING-clause is allowed without the GROUP-BY-clause
• JOIN: CROSS is synonymous with INNER
• Terminology:
• • event - time-scheduled statement
  • trigger - data-change-initiated statement
     
  • cluster (aka synchronous replication) - multiple machines for redundant reliability
  • partition - multiple machines for spreading storage resources
  • replication (asynchronous, aka master and slave) - multiple machines for spreading bandwidth and processing

Page 3: Functions A-L

• DES_ENCRYPT() and DES_DECRYPT() swapped
• IF() function should be marked a power feature
• NOT should not be marked a power feature
• LEAST() stutter: contrast MIN(z)

Page 4: Functions M-Z

• ORD() should return an n number, not a b boolean. (Thanks Mark F.)
• SUBSTRING() close parentheses is not optional (it should be on the baseline)
• YEARWEEK() doesn't work for natural-year nschemes
• Data Conversion:  x ← CAST (n AS DOUBLE);  (wrong: s ←) (Thanks to Gregory)

 

Revision History

January 2008 Edition (card and chart)
	Version 6 features color coded!  Both of them!!  (Seriously, ten, counting generously.)
	ONLINE or OFFLINE for ALTER, CREATE, DROP INDEX
	COLUMN_FORMAT, STORAGE column options (cops)
	LOCK TABLE now has options IN SHARE MODE or IN EXCLUSIVE MODE
	BACKUP TABLE and RESTORE TABLE were removed (unavailable in version 6)
	COMMENT 'string' for CREATE INDEX and ALTER TABLE ADD INDEX
	LOAD XML, like LOAD DATA, with new ROWS IDENTIFIED BY option
	WEIGHT_STRING() new function
	missing functions BENCHMARK(), COERCIBILITY(), FIND_IN_SET()
	Legend and Name sections stand out like traffic signs (thx Norell G.)
	/*!50206 mysql-version-specific-non-comment-code */
	numeric ranges for integer types (instead of the number of significant digits)
	help: visibone.com/sql/tech footer
 	visually integrated legend annex (i.e. the two boxes got smoooshed together)
	more terminology (event, trigger)
	some different color and symbol schemes (green=act, yellow=inform, red=danger)
	two charts now, for statements and functions (also two sizes and two finishes each)
	(Many thanks to everyone at MySQLCamp 2007, New York, for lots of great feedback)

August 2007 Edition (card only, the chart was not revised)
	INSERT, REPLACE - SET syntax has no parentheses
	GRANT - WITH clause optional
	SELECT - HAVING clause possible without GROUP BY clause
	SUBSTRING() - closing parens not optional
	CAST(n AS DOUBLE) - result type x not s
	annotations - IF is common, NOT is not so much
	orange border:  Legend, Names, Types
	orange title: DELETE, INSERT, UPDATE
	LEAST() stutter removed
	IFNULL(), NULLIF() zSymbolForNull -> zSubstituteForNull
	YEARWEEK() gotcha nschemes 0,1,4,5
	LEFT(), RIGHT() show equivalent SUBSTRING() instead of MID()
	'time' -> 'dt' (date & time) in event-definition
	ROUND() combine 2 forms into 1, mention it's a 'formatter'
	INNER JOIN identical to CROSS JOIN
	CREATE FUNCTION/PROCEDURE, DECLARE, h-after is comma-separated, not space-separated
	interval gotcha - never plural
	SELECT "FROM DUAL" allowed for cosmetics
	version 6.0.0-alpha (ENGINE Falcon)	
	SELECT GROUP BY and ROLLUP clues
	space-separated-lists are indicated more like comma-lists are indicated
	STD() more explicit aliases: STDDEV(), STDDEV_POP()
	ORD() returns n (numeric) not b (boolean)
	examples return TRUE for RLIKE(), REGEXP(), LIKE(), SOUNDS_LIKE()
	
March 2007 Edition - MySQL Card and Chart - FIRST ISSUE