Skip to content

Table or Column's name wrapped with double quotes in stead of single quote

Adrian Voo edited this page Nov 30, 2018 · 1 revision

It is the behavior of MySql Server which can be configured either as default (GLOBAL) behavior or temporary (SESSION) behavior of MySQL server.

Here is the SQL statements that you can try out yourself:

Example 1: Export table structure with double quotes:

set session sql_mode=ANSI_QUOTES;
show create table `configkey`;

output:

CREATE TABLE "configkey" (
  "key" varchar(100) NOT NULL,
  "value" text,
  PRIMARY KEY ("key")
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Example 2: Export table structure with single quote

set session sql_mode=traditional;
show create table `configkey`;

output:

CREATE TABLE `configkey` (
  `key` varchar(100) NOT NULL,
  `value` text,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

For more information, please refer to official MySQL documentation under the topic: SQL-MODE https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_sql-mode https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

If you see the column names are wrapped with double quotes by default, it's most probably your MySQL server is configured to react in that way by default. You may consult your MySQL server administrator or provider.

Alternatively, you can configure the SQL_MODE each time manually before executing MySqlBackup.NET. Below is an example:

using (MySqlConnection conn = new MySqlConnection(constring))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        using (MySqlBackup mb = new MySqlBackup(cmd))
        {
            cmd.Connection = conn;
            conn.Open();

            cmd.CommandText = "set session sql_mode=traditional;";
            cmd.ExecuteNonQuery();

            mb.ExportToFile(file);
            conn.Close();
        }
    }
}