23.9 Using MySQL

This section describes particular issues in Common SQL with MySQL databases.

23.9.1 Connection specification

See 23.2.6 Connecting to MySQL for information about MySQL specific extensions for the connection-spec passed to connect.

23.9.2 Case of table names and database names

MySQL is case sensitive on table names and database names when the server is on a Unix machine. MySQL does not automatically change raw names to uppercase as specified by the SQL standard. However, Common SQL is geared towards uppercasing all names, so this may cause some mismatches. In general, Common SQL uppercases strings, and uses symbol names, which are normally uppercase, as-is.

One solution, possible only if you control the naming of tables and databases, is to make them all have the same case. If this is uppercase, that suffices. If it is lowercase, you need to set the variable lower_case_table_names in the configuration of the server.

If you cannot make all the names the same case, you have to get the case right. This can be achieved in several ways:

1. Specify tables names using strings, for example:

   (sql:select [*] :from ["TableNAMEwithVARIABLEcase"])
   

   Note that this does not work in LispWorks 4.4 and previous versions.

2. Pass the Lisp string directly:

   (sql:select [*] :from "TableNAMEwithVARIABLEcase")
   

   Note that in this case the table name is passed to the database inside double quotes. That works only when the mode of the Common SQL connection contains ANSI_QUOTES (which is the default, see 23.9.4 SQL mode for details).

3. Specify table names as escaped symbols:

   (sql:select [*] :from [|TableNAMEwithVARIABLEcase|])
   

4. Construct the whole query string and pass it to query rather than using select:

   (sql:query "select * from TableNAMEwithVARIABLEcase")
   

23.9.3 Encoding (character sets in MySQL).

You can specify the encoding to be used by passing the :encoding argument to connect. Common SQL supports various encodings for MySQL as documented in connect.

The default is to use the default for the particular MySQL installation.

23.9.4 SQL mode

Because Common SQL is geared towards ANSI SQL, by default it connects in ANSI mode. If another mode is required, it can be set at connection time.

For example, to make MySQL treat quotes as in ANSI without setting other ANSI features, do:

(sql:connect "me/mypassword/mydb"
             :sql-mode "ANSI_QUOTES")

See the description of the :sql-mode argument to connect for details.

23.9.5 Meaning of the :owner argument to select

In the Common SQL MySQL interface, the value of the select keyword argument :owner is interpreted to select a database name.

23.9.6 Special considerations for iteration functions and macros

This section describes particular issues when fetching multiple records using Common SQL with MySQL databases.

23.9.6.1 Fetching multiple records

The function map-query and the macros do-query, simple-do-query and loop with each record use internally mysql-use-query, which means that the underlying MySQL code brings the data from the server one record at a time. With a small number of records, it may be preferable to bring all the data immediately instead. This can be done by passing the argument get-all, as follows:

(sql:map-query nil 'print
               "select forname,surname from people"
               :get-all t)
 
(sql:do-query
 ((forname surname) "select forname,surname from people"
  :get-all t) 
 body)
 
(sql:simple-do-query 
 (list "select forname,surname from people"
       :get-all t)
 body)
 
(loop for (forname surname) being each record 
      "select forname,surname from people"
      get-all t
      body)

23.9.6.2 Aborting queries which fetch many records

In the MySQL interface there is no way to abort a query when part way through it. When any of the iterations above stops before reaching its end, the underlying code retrieves all the records to the end of the query (though without converting them to Lisp objects). If the query found many records, that may be an expensive (that is, time consuming) operation.

It is possible to avoid this inefficiency by passing the argument not-inside-transaction. If not-inside-transaction is true then when a query is aborted, then LispWorks closes the database connection and reopens it, rather than retrieving all the remaining records.

(sql:map-query nil 'print 
               "select forname,surname from people"
               :get-all t
               :not-inside-transaction t)

Note that this will lose any state associated with the connection, and so not-inside-transaction should only be used with care.

23.9.7 Table types

By default, create-table creates tables of the default type. This behavior can be overridden by the connect keyword arguments :default-table-type and :default-table-extra-options, and the :type and :extra-options keyword arguments to create-table.

If type is passed to create-table or default-table-type was passed to connect, it is used as the argument to the "keyword" TYPE in the SQL statement:

create table MyTable (column-specs) TYPE = type-value

If extra-options is passed to create-table or default-table-extra-options was passed to connect, it is appended in the end of the SQL statement above.

connect with default-table-type and create-table with type also accept the keyword argument :support-transactions. When support-transactions is true, these functions will attempt to make tables that support transactions. It does this by using the type innodb.

23.9.8 Rollback errors

The default value of the connect keyword argument :signal-rollback-errors is determined by the value of the :default-table-type argument. If default-table-type is :support-transactions or "innodb" or "bdb", then the default value for :signal-rollback-errors is t, otherwise the default value is nil.

23.9.9 Types of values returned from queries

Common SQL uses the MySQL mechanism that returns values as strings.

By default, Common SQL converts these strings to the appropriate Lisp type corresponding to the column type (or more accurately, the type of the field in the query) according to MySQL type mapping.

However, if you specify the result type as :string, this eliminates the conversion and the return value is simply the string retrieved by MySQL. For information about specifying the result type for a column (or multiple columns) in a query, see 23.3.1.1 Querying.

Each of the five date-like types (that is, Date, Datetime, Timestamp, Time and Year) can have result type :date, :date-string or :datetime-string with the following effects:

All the numeric types can have result type :int, :single-float or :double-float, causing the appropriate conversion. No check is made on whether the result is actually useful.

String types can have result type :binary, which returns an array.

23.9.10 Autocommit

Common SQL sets autocommit to 0 when it opens a MySQL connection.