mysql 大小写敏感

mysql大小写敏感分表名大小写敏感和表字段大小写敏感

 

1、表名大小写敏感

mysql> show variables like 'lower%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| lower_case_file_system | OFF   |

| lower_case_table_names | 0     |

+------------------------+-------+

2 rows in set (0.00 sec)

 

lower_case_table_names = 0 代表表名区分大小写敏感

 

2、表字段大小写敏感设置

mysql是不支持表里面字段大小写敏感的

需要在创建表的时候指定

 

mysql>  create table aa (a varchar(20) BINARY  , c varchar(20)) ;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show create table aa;

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                |

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+

| aa    | CREATE TABLE `aa` (

  `a` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

  `c` varchar(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

 

mysql> insert into aa values('A','ss');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into aa values('A','s1');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into aa values('A','s2');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into aa values('a','s2');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into aa values('a','s4');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from aa where a='a';

+------+------+

| a    | c    |

+------+------+

| a    | s2   |

| a    | s4   |

+------+------+

2 rows in set (0.00 sec)

 

mysql> select * from aa where a='A';

+------+------+

| a    | c    |

+------+------+

| A    | ss   |

| A    | s1   |

| A    | s2   |

+------+------+

3 rows in set (0.00 sec)

 

 

若表已创建，则需要更改表结构

例如

mysql> show create table trade_payment_channel;

 

| Table                 | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |

 

| trade_payment_channel | CREATE TABLE `trade_payment_channel` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',

  `channel_name` varchar(32) DEFAULT NULL COMMENT '支付渠道名称',

  `channel_code` varchar(8) DEFAULT NULL COMMENT '支付渠道代码(建议与恒生配置字典相同)',

  `channel_status` int(11) DEFAULT NULL COMMENT '状态（0：禁用， 1：启用）',

  `create_time` datetime DEFAULT NULL COMMENT '创建时间',

  `modify_time` datetime DEFAULT NULL COMMENT '修改时间',

  PRIMARY KEY (`id`),

  UNIQUE KEY `channel_code_unique` (`channel_code`)

) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4                                             |

 

1 row in set (0.00 sec)

 

mysql> alter table trade_payment_channel modify channel_code varchar(8) binary DEFAULT NULL COMMENT '支付渠道代码(建议与恒生配置字典相同)';

Query OK, 16 rows affected (0.02 sec)

Records: 16  Duplicates: 0  Warnings: 0

 

mysql> show create table trade_payment_channel;

 

| Table                 | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

| trade_payment_channel | CREATE TABLE `trade_payment_channel` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',

  `channel_name` varchar(32) DEFAULT NULL COMMENT '支付渠道名称',

  `channel_code` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '支付渠道代码(建议与恒生配置字典相同)',

  `channel_status` int(11) DEFAULT NULL COMMENT '状态（0：禁用， 1：启用）',

  `create_time` datetime DEFAULT NULL COMMENT '创建时间',

  `modify_time` datetime DEFAULT NULL COMMENT '修改时间',

  PRIMARY KEY (`id`),

  UNIQUE KEY `channel_code_unique` (`channel_code`)

) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4                                             |

+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

alter table trade_payment_channel modify channel_code varchar(8) binary DEFAULT NULL COMMENT '支付渠道代码(建议与恒生配置字典相同)',

 

参考链接 https://www.cnblogs.com/wzmenjoy/p/4244545.html

              https://www.cnblogs.com/wzmenjoy/p/4244545.html