How to change SQL_MODE in MySQL Permanently
- Article
- Comment
Introduction
This is very simple article to set the mysql mode permanently inside your Mysql Server 5.7 or above versions. I tried some articles and finally found this to helpfull for someone who came with same issue. Usually some queries needs to change the sql mode permanently, Otherwise you need to write set mode everytime. I got stressed to set sql_mode on every query. So I came up with a solution for “How to change SQL_MODE in MySQL Permanently?”. Hope this will helpfull to someone who comes with same issue.
Solution
I am here using Ubuntu. So let me give by stating with Ubuntu.
Linux(Ubuntu) & MySQL:
Ubuntu and other debian based linux and some of the other Linux operating systems are similar to this solution. Just goto this directory /etc/mysql/mysql.conf.d/mysqld.conf.
Lets add a sql_mode permanently here to take effect. under [mysqld]
just add one more line like the below.
sql_mode="STRICT_TRANS_TABLES"
Thats it. You can add more than one mode
sql_mode='TRADITIONAL'
Windows & MySQL
Its easy for windows users, There you might have XAMPP or WAMPP. Within it C:/xampp/mysql/mysqld.conf . And do the same of linux and add the sql_mode=”;
Other Modes
Here you can set other modes as well. Some of the modes I have listed here.
ALLOW_INVALID_DATES
ANSI_QUOTES
ERROR_FOR_DIVISION_BY_ZERO
HIGH_NOT_PRECEDENCE
IGNORE_SPACE
NO_AUTO_CREATE_USER
NO_AUTO_VALUE_ON_ZERO
NO_BACKSLASH_ESCAPES
NO_DIR_IN_CREATE
NO_ENGINE_SUBSTITUTION
NO_FIELD_OPTIONS
NO_KEY_OPTIONS
NO_TABLE_OPTIONS
NO_UNSIGNED_SUBTRACTION
NO_ZERO_DATE
NO_ZERO_IN_DATE
ONLY_FULL_GROUP_BY
PAD_CHAR_TO_FULL_LENGTH
PIPES_AS_CONCAT
REAL_AS_FLOAT
STRICT_ALL_TABLES
STRICT_TRANS_TABLES
TRADITIONAL
May be I have missed some modes. But you can refere with mysql site to get it.
