asked on

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

what does this command do

and how can I see the current mode

I do not want to run this command if it could affect my current mysql

this is the entire insert tables command from lynda.com php mysql tutorial

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE IF NOT EXISTS `address` (
  `address_id` int(11) NOT NULL AUTO_INCREMENT,
  `address_type_id` int(11) NOT NULL,
  `time_created` int(11) NOT NULL,
  `time_updated` int(11) NOT NULL,
  `street_address_1` varchar(255) NOT NULL,
  `street_address_2` varchar(255) NOT NULL,
  `city_name` varchar(255) NOT NULL,
  `subdivision_name` varchar(255) NOT NULL,
  `postal_code` varchar(20) NOT NULL,
  `country_name` varchar(255) NOT NULL,
  PRIMARY KEY (`address_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

INSERT INTO `address` (`address_id`, `address_type_id`, `time_created`, `time_updated`, `street_address_1`, `street_address_2`, `city_name`, `subdivision_name`, `postal_code`, `country_name`) VALUES
(1, 1, 762483360, 0, '300 Oracle Parkway', '', 'Redwood City', 'California', '94065', 'United States');

CREATE TABLE IF NOT EXISTS `location` (
  `location_id` int(11) NOT NULL AUTO_INCREMENT,
  `city_name` varchar(255) NOT NULL,
  `city_latitude` double NOT NULL,
  `city_longitude` double NOT NULL,
  `subdivision_name` varchar(255) NOT NULL,
  `postal_code` varchar(20) NOT NULL,
  PRIMARY KEY (`location_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO `location` (`location_id`, `city_name`, `city_latitude`, `city_longitude`, `subdivision_name`, `postal_code`) VALUES
(1, 'Townsville', 38.7153408676386, -75.0866805016994, 'State', '12345'),
(2, 'Villageland', 33.1156463623047, -117.120277404785, 'Region', '67890'),
(3, 'Hamlet', 43.6666296, -92.9746367, 'Territory', '34567'),
(4, 'Redwood City', 37.5311965942383, -122.2646484375, 'California', '94065');

Open in new window

Argenti

Here is the MySQL reference manual, saying that

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.

To determine the current global or session sql_mode value, use the following statements:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

Your SQL script that you published does nothing but to create two tables and fill them with data.

rgb192

ASKER

I ran everything except
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

both results are empty

since I do not want to have these rules in the current database
I chose not to run
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

but I still dont know which type of sql_mode that I am using

ASKER CERTIFIED SOLUTION

Argenti

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rgb192

ASKER

so the default mode is already empty?

Argenti

Yes

rgb192