2

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;
L.G
  • 1,458
  • 4
  • 14
  • 24
  • `SQL_MODE` https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html `time_zone` https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_time_zone.. `SET` is MySQL's way of setting system variable or user variable. https://dev.mysql.com/doc/refman/8.0/en/set-statement.html "what are the possible choices when creating the database?" see source https://dev.mysql.com/doc/refman/8.0/en/create-database.html – Raymond Nijland Sep 06 '18 at 17:13
  • Ok and what means time_zone = "+00:00" ? Thanks – L.G Sep 06 '18 at 17:19
  • `+00:00` means UTC timezone. – Raymond Nijland Sep 06 '18 at 17:26

2 Answers2

2

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?

M4HdYaR
  • 1,124
  • 11
  • 27
0

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.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360