Home > Database > Mysql Tutorial > body text

How to use mysql strict mode Strict Mode

WBOY
Release: 2023-05-26 15:10:15
forward
2437 people have browsed it

    1. How to turn on and off Strict Mode

    Find the my.cnf (my.ini for Windows system) file in the mysql installation directory

    Adding STRICT_TRANS_TABLES to sql_mode means turning on strict mode. If it is not added, it means non-strict mode. After modification, restart mysql.

    For example, this means that strict mode is turned on:

    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

    2.Strict Mode Function Description

    • ##Does not support inserting null values ​​into not null fields

    • Does not support inserting "value" into self-increasing fields

    • Does not support default values ​​for text fields

    3. Example :

    Create a data table to facilitate testing

    CREATE TABLE `mytable` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `name` varchar(20) NOT NULL,
     `content` text NOT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    Copy after login

    1. Not null field inserts null value test

    Insert a record, the value of name is null

    Execute in non-strict mode

    mysql> insert into mytable(content) values('programmer');
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    mysql> select * from mytable;
    +----+------+------------+
    | id | name | content    |
    +----+------+------------+
    |  1 |      | programmer |
    +----+------+------------+
    1 row in set (0.00 sec)
    Copy after login

    Execution is successful, the value of name is automatically converted to "

    Execute in strict mode

    mysql> insert into mytable(content) values('programmer');
    ERROR 1364 (HY000): Field 'name' doesn't have a default value
    Copy after login

    The execution failed, prompting that the field name cannot be a null value

    2. Insert "value test for auto-increment field"

    Insert "value for id field

    Executed in non-strict mode

    mysql> insert into mytable(id,name,content) value('','fdipzone','programmer');
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    mysql> select * from mytable;
    +----+----------+------------+
    | id | name     | content    |
    +----+----------+------------+
    |  1 | fdipzone | programmer |
    +----+----------+------------+
    1 row in set (0.00 sec)
    Copy after login

    Executed successfully

    Executed in strict mode

    mysql> insert into mytable(id,name,content) value('','fdipzone','programmer');
    ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1
    Copy after login

    Execution failed, prompting that the field id cannot be "

    mysql> insert into mytable(id,name,content) value(null,'fdipzone','programmer');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from mytable;
    +----+----------+------------+
    | id | name     | content    |
    +----+----------+------------+
    |  1 | fdipzone | programmer |
    +----+----------+------------+
    1 row in set (0.00 sec)
    Copy after login

    If the field id is null, the execution can be successful

    3. Text field default value test

    Create one Data table mytable, where text sets the default value default=”

    Executed in non-strict mode

    mysql> CREATE TABLE `mytable` (
        ->  `id` int(11) NOT NULL AUTO_INCREMENT,
        ->  `name` varchar(20) NOT NULL,
        ->  `content` text NOT NULL default '',
        ->  PRIMARY KEY (`id`)
        -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    Query OK, 0 rows affected, 1 warning (0.03 sec)
    
    mysql> show tables;
    +------------------------------+
    | Tables_in_test_version       |
    +------------------------------+
    | mytable                      |
    +------------------------------+
    Copy after login

    Executed successfully

    Execution in strict mode

    mysql> CREATE TABLE `mytable` (
        ->  `id` int(11) NOT NULL AUTO_INCREMENT,
        ->  `name` varchar(20) NOT NULL,
        ->  `content` text NOT NULL default '',
        ->  PRIMARY KEY (`id`)
        -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    ERROR 1101 (42000): BLOB/TEXT column 'content' can't have a default value
    Copy after login

    Failed to execute, prompting that the content field is of type TEXT and the default value cannot be used.

    Using MySQL's strict mode can enhance data security, but the disadvantage is that it reduces the compatibility of empty data into the database. It is recommended that the development environment use strict mode to improve code quality and data rigor.

    The above is the detailed content of How to use mysql strict mode Strict Mode. For more information, please follow other related articles on the PHP Chinese website!

    Related labels:
    source:yisu.com
    Statement of this Website
    The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
    Popular Tutorials
    More>
    Latest Downloads
    More>
    Web Effects
    Website Source Code
    Website Materials
    Front End Template