CODEWITHSUNDEEP
mysqlsqldatabasetimezonesql-mode
L.G
L.G

Reputation: 1556

SQL_MODE and TIME_ZONE when creating DATABASE

What do these options mean, and what are the possible choices when creating the database?

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

#------------------------------------------------------------
#        Script MySQL.
#------------------------------------------------------------

DROP DATABASE if exists BASETEST;
CREATE DATABASE IF NOT EXISTS BASETEST DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;
USE BASETEST;

Upvotes: 2

Views: 2850

Answers (2)

Álvaro González
Álvaro González

Reputation: 146563

The SET statement is used here:

to assign values to variables that affect the operation of the server or clients

Neither the statement nor the variables are specific to database creation. In fact I don't think they should have any noticeable effect:

  • You cannot configure AUTO_INCREMENT handling in a per database basis (only per server or per session).
  • A database does not have a time zone (the server or current session do).

It's common though that tools that generate SQL dumps set some variables to configure session and get a predictable environment to run commands. Such variables tend to come from a template and aren't customised for script content.

Upvotes: 0

M4HdYaR
M4HdYaR

Reputation: 1164

SET set's one of MySQL variables. Some of them are system variables some of them are user variables ...

SET SQL_MODE:

SQL_MODE is a system variables and you can see all possible modes in the documentation.

NO_AUTO_VALUE_ON_ZERO :

NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number.

SET time_zone :

SET time_zone = "+00:00" Sets the session timezone on UTC.

Read more : How do I set the time zone of MySQL?

Upvotes: 3

Related Questions