SQL：ENUM(‘Y‘, ‘N‘)

旧城等待， 2022-03-26 07:37 72阅读 0赞

已经知道

CREATE TABLE IF NOT EXISTS `arguse`.`userinfo` (
      `uid` BIGINT(20) NOT NULL AUTO_INCREMENT,
      `uname` VARCHAR(45) NOT NULL,
      `ugrant` INT(11) NOT NULL,
      `ukind` ENUM('Y', 'N') NOT NULL DEFAULT 'N',
      `upassword` VARCHAR(45) NOT NULL,
      PRIMARY KEY (`uid`),
      INDEX `uname` (`uname` ASC))
    ENGINE = InnoDB;
    
    (root@localhost) [arguse]> show variables like '%sql_mode%';
    +---------------+-----------------------------------------------------------------------------------------------------------------------+
    | Variable_name | Value                                                                                                                 |
    +---------------+-----------------------------------------------------------------------------------------------------------------------+
    | sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
    +---------------+-----------------------------------------------------------------------------------------------------------------------+

此时插入数据

INSERT INTO `arguse`.`userinfo` (`uid`, `uname`, `ugrant`, `ukind`, `upassword`) VALUES (DEFAULT, 'admin', 0, '1', '123456')
    INSERT INTO `arguse`.`userinfo` (`uid`, `uname`, `ugrant`, `ukind`, `upassword`) VALUES (DEFAULT, 'admin', 0, 'Y', '123456');
    INSERT INTO `arguse`.`userinfo` (`uid`, `uname`, `ugrant`, `ukind`, `upassword`) VALUES (DEFAULT, 'admin', 1, 1, '123456');  -- 不建议
    
    结果：
    # uid, uname, ugrant, ukind, upassword
    '1', 'admin', '0', 'Y', '123456'
    '2', 'admin', '0', 'Y', '123456'
    '4', 'admin', '1', 'Y', '123456'

奇怪的2

INSERT INTO `arguse`.`userinfo` (`uid`, `uname`, `ugrant`, `ukind`, `upassword`) VALUES (DEFAULT, 'noadmin', 11,  0, '123456'); ;  -- 不可以，-- Error Code: 1265. Data truncated for column 'ukind' at row 1， 数据长度不够
    INSERT INTO `arguse`.`userinfo` (`uid`, `uname`, `ugrant`, `ukind`, `upassword`) VALUES (DEFAULT, 'noadmin', 11,  3, '123456');  -- 不可以，-- Error Code: 1265. Data truncated for column 'ukind' at row 1， 数据长度不够
    INSERT INTO `arguse`.`userinfo` (`uid`, `uname`, `ugrant`, `ukind`, `upassword`) VALUES (DEFAULT, 'noadmin', 11,  4, '123456');;  -- 不可以，-- Error Code: 1265. Data truncated for column 'ukind' at row 1， 数据长度不够
    INSERT INTO `arguse`.`userinfo` (`uid`, `uname`, `ugrant`, `ukind`, `upassword`) VALUES (DEFAULT, 'noadmin', 11, '3', '123456'); -- 不可以，-- Error Code: 1265. Data truncated for column 'ukind' at row 1， 数据长度不够
    
    
    
    
    > INSERT INTO `arguse`.`userinfo` (`uid`, `uname`, `ugrant`, `ukind`, `upassword`) VALUES (DEFAULT, 'noadmin', 11, 2, '123456');
    Query OK, 1 row affected (0.10 sec)
    (root@localhost) [arguse]> select * from userinfo;
    +-----+---------+--------+-------+-----------+
    | uid | uname   | ugrant | ukind | upassword |
    +-----+---------+--------+-------+-----------+
    |   1 | noadmin |     11 | N     | 123456    |
    +-----+---------+--------+-------+-----------+

奇怪的0

truncate userinfo;
    
    INSERT INTO `arguse`.`userinfo` (`uid`, `uname`, `ugrant`, `ukind`, `upassword`) VALUES (DEFAULT, 'noadmin', 11, '0', '123456');
    INSERT INTO `arguse`.`userinfo` (`uid`, `uname`, `ugrant`, `ukind`, `upassword`) VALUES (DEFAULT, 'noadmin', 11, 'N', '123456');
    INSERT INTO `arguse`.`userinfo` (`uid`, `uname`, `ugrant`, `ukind`, `upassword`) VALUES (DEFAULT, 'noadmin', 11, '2', '123456');
    -- INSERT INTO `arguse`.`userinfo` (`uid`, `uname`, `ugrant`, `ukind`, `upassword`) VALUES (DEFAULT, 'noadmin', 11, 'N', '123456');
    -- INSERT INTO `arguse`.`userinfo` (`uid`, `uname`, `ugrant`, `ukind`, `upassword`) VALUES (DEFAULT, 'noadmin', 11, '2', '123456');
    (root@localhost) [arguse]> select * from userinfo;
    +-----+---------+--------+-------+-----------+
    | uid | uname   | ugrant | ukind | upassword |
    +-----+---------+--------+-------+-----------+
    |   1 | noadmin |     11 |       | 123456    |
    |   2 | noadmin |     11 | N     | 123456    |
    |   3 | noadmin |     11 | N     | 123456    |
    +-----+---------+--------+-------+-----------+