The General MySQL Syntax section of the MySQL 5.6 Developer certification exam is a bit meatier than the MySQL Architecture section covered in my last post, but it’s still likely to be very familiar to experienced MySQL developers (or DBAs):

• Explain MySQL implementation of identifiers including case sensitivity, qualified names, aliases and use of reserved words
• Identify MySQL data type properties and appropriate usage
• Recognize and use common functions and expressions for all MySQL data types
• Identify and use comment syntax
• Describe and utilize prepared statements
• Describe transactions and transaction isolation levels and the impact they have on database behavior

MySQL Identifiers

• MySQL treats some words differently, and there’s a list of reserved words found in the manual.  Not a chance I’ll commit this list to memory.
• You can quote these reserved words for use as identifiers, but it’s better to avoid them entirely – it will cause problems later.
• There’s a handful of simple rules for valid identifiers; it’s worth committing them to memory.  I generally boil them down to:
  • 64 characters maximum
  • If using ASCII symbols other than $ or _, quote it.
  • If using whitespace or control characters, quote it.
  • Can’t have leading or trailing whitespace characters (UPDATE: only trailing whitespace characters are restricted).
  • If using all numbers for an identifier, quote it.
• Database and table names are represented by files on disk; identifier name case-sensitivity for these objects is tied to the behavior of the filesystem
  • The filesystem-dependent behavior can be overridden with lower_case_table_names – but be careful to prevent stranding pre-existing objects with upper-case characters in the identifiers.
• Know how to alias objects in SQL statements and their scope.  Review the documentation dedicated to problems with aliases.

Data type properties

Picking the right data type for application needs is a fundamental step towards a successful MySQL implementation.  The entire data types section of the manual is a masterpiece (thanks to the awesome Docs team!), and should be read in full, but these are my highlights that I’m hitting in my certification exam prep work:

• Review the addition of fractional-section support to temporal data types in MySQL 5.6.
• Become familiar with data type storage requirements.
• Know the default values for various data types.
• Understand the various attributes for numeric data types.
• Demonstrate the automatic initialization and update properties of the TIMESTAMP and DATETIME data type.
• Know the range limitations of various data types.

Expressions and functions

Chapter 12 of the MySQL reference manual is key, here.  If you want a single reference of all operators and functions, this page is great.  It’s hard to narrow this down, honestly – yet at the same time, I’m unlikely to remember functions like CRC32() or MAKE_SET().  I’ll focus on:

• Comparison operators
• The := assignment operator
• Time and date functions
• FULLTEXT search functions
• Certain information functions:
  • CONNECTION_ID()
  • CURRENT_USER(), USER() – and how they differ
  • FOUND_ROWS()
  • LAST_INSERT_ID()
  • ROW_COUNT()
• Aggregate functions

I didn’t include string functions, even though there are a number of fundamental functions there.  Chances are, if you’ve used MySQL for a while as a developer, you’re sufficiently comfortable with these functions.

Comments

There’s not much to be said about comments beyond what this manual page says.  Be familiar with all the different comment syntax, and particularly those which trigger execution solely in MySQL or on certain versions.

Prepared Statements

The manual page for prepared statements is here.  Know how to create, execute and deallocate prepared statements.  Make sure you understand the security aspects of prepared statement usage, and that prepared statements are session-specific.  There’s some useful examples of prepared statements on the referenced manual page, including how to use prepared statements to dynamically define and execute arbitrary SQL.  This can be useful particularly in the context of stored routines (discussed later).

Transactions

There’s a bit to understand about transactions in MySQL.  Start with the basics here, noting how START TRANSACTION, BEGIN and autocommit variable state interact.  You should know which storage engines offer transaction support (and how to evaluate this for any storage engine), which statements are not transactional (cannot be rolled back), and which statements cause an implicit commit of pending transactions.  Understanding the explicit table lock syntax here is helpful.  I’m skipping manual sections on savepoints and XA transactions, under the hope that I really only need to know that such support exists.

Practice Questions

Just as before, I make no assertion that the practice questions provided here are representative of what candidates will encounter on the exam.  In fact, they mostly represent what I consider to be interesting – but likely edge-case or tricky – behavior encountered while doing my own exam preparation.  I wouldn’t focus on how many questions you get right – I think this is more a trivia contest that keeps me (and hopefully you) interested and looking to delve a little deeper into the topics, or re-read the relevant documentation with additional context.  I hope you find them useful.