MySQL字符集大小写不敏感(Windows平台)&整数类型（精确值）Integer、int、smallint、tinyint、mediumint、bigint(ERROR 1264 (22003))

最新推荐文章于 2022-02-23 17:18:22 发布

Be_insighted

最新推荐文章于 2022-02-23 17:18:22 发布

阅读量488

点赞数

分类专栏：数据库文章标签： MySQL 大小写不敏感 Out of range ERROR 1264 smallint

本文链接：https://blog.csdn.net/Be_insighted/article/details/119844708

版权

数据库专栏收录该内容

88 篇文章 3 订阅

订阅专栏

utf8_bin

将字符串中的每一个字符用二进制数据存储，区分大小写。

utf8_genera_ci

不区分大小写，ci为case insensitive的缩写，即大小写不敏感。

utf8_general_cs

区分大小写，cs为case sensitive的缩写，即大小写敏感。

COLLATE排序规则，utf8_bin，二进制排序区分大小写

CREATE TABLE `course` (
  `cid` int(11) DEFAULT NULL,
  `cname` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `tid` int(11) DEFAULT NULL,
  UNIQUE KEY `idx_cid_cname` (`cid`,`cname`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

在Windows平台，MySQL默认是大小写不敏感的，Linux平台是默认敏感的。这个是跟操作系统的文件系统特性有关的，Windows是不敏感的，可以创建同名文件夹(英文的)大小写试一试！

修改某张表的某个字段大小敏感

alter table 表名称 modify 列名称 varchar(6) collate utf8_bin;

Mysql中控制大小写敏感由参数lower_case_table_names控制

mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 1     |
+------------------------+-------+

lower_case_file_system

此变量描述数据目录所在的文件系统上文件名的区分大小写。 OFF表示区分大小写，ON表示不区分大小写。此变量是只读的，因为它反映了文件系统属性并设置它对文件系统没有影响。

lower_case_table_system

该参数为静态，可设置为0、1、2。

值	意义
`0`	表和数据库名称使用CREATE TABLE or CREATE DATABASE 语句中指定的字母大小写存储在磁盘上。名称比较区分大小写。如果你有不区分大小写的文件名（如Windows或Mac系统）的系统上运行MySQL这个变量设置为0。如果--lower-case-table-names=0 在不区分大小写的文件系统上将此变量默认为 0 ，`MyISAM`使用不同的字母访问表名，则可能会导致索引损坏。
`1`	表名以小写形式存储在磁盘上，名称比较不区分大小写。MySQL 在存储和查找时将所有表名转换为小写。此行为也适用于数据库名称和表别名。
`2`	表和数据库名称使用 CREATE TABLE or CREATE DATABASE 语句中指定的字母存储在磁盘上，但 MySQL 在查找时将它们转换为小写。名称比较不区分大小写。这仅适用于不区分大小写的文件系统！ `InnoDB`表名和视图名以小写形式存储，如 `lower_case_table_names=1`.

If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.

在 INFORMATION_SCHEMA 搜索中使用排序规则

表中的字符串列INFORMATION_SCHEMA的排序规则为utf8_general_ci，不区分大小写。但是，对于对应于文件系统中表示的对象的值，例如数据库和表，INFORMATION_SCHEMA字符串列中的搜索可以区分大小写或不区分大小写，具体取决于底层文件系统的特性和lower_case_table_names 系统变量。例如，如果文件系统区分大小写，则搜索可能区分大小写。本节描述了这种行为以及如何在必要时修改它；

假设查询在该SCHEMATA.SCHEMA_NAME列中搜索 test数据库。在 Linux 上，文件系统区分大小写，因此 SCHEMATA.SCHEMA_NAME与 'test'匹配的比较，但与'TEST'不进行比较：

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
       WHERE SCHEMA_NAME = 'test';
+-------------+
| SCHEMA_NAME |
+-------------+
| test        |
+-------------+

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
       WHERE SCHEMA_NAME = 'TEST';
Empty set (0.00 sec)

不区分大小写时，比较时匹配'test'和 'TEST'：

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
       WHERE SCHEMA_NAME = 'test';
+-------------+
| SCHEMA_NAME |
+-------------+
| test        |
+-------------+

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
       WHERE SCHEMA_NAME = 'TEST';
+-------------+
| SCHEMA_NAME |
+-------------+
| TEST        |
+-------------+

要执行不区分大小写的搜索，请一起使用 COLLATE、INFORMATION_SCHEMA和列名(SCHEMA_NAME COLLATE utf8_general_ci)

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
       WHERE SCHEMA_NAME COLLATE utf8_general_ci = 'test';
+-------------+
| SCHEMA_NAME |
+-------------+
| test        |
+-------------+

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
       WHERE SCHEMA_NAME COLLATE utf8_general_ci = 'TEST';
+-------------+
| SCHEMA_NAME |
+-------------+
| test        |
+-------------+

整数类型（精确值）——INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT

类型	存储（字节）	有符号的最小值	最小值无符号	最大值有符号	最大值无符号
`tinyint`	1	`-128`	`0`	`127`	`255`
smallint	2	`-32768`	`0`	`32767`	`65535`
`mediumint`	3	`-8388608`	`0`	`8388607`	`16777215`
int	4	`-2147483648`	`0`	`2147483647`	`4294967295`
bigint	8	$-2^{63}$	`0`	$2^{63}-1$	$2^{64}-1$

创建表时将表的整数数值列声明为无符号整数

CREATE TABLE `tb_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_name` varchar(20) NOT NULL COMMENT '用户名',
`password` varchar(20) NOT NULL COMMENT '密码',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`salary` smallint(5) UNSIGNED DEFAULT 2000 COMMENT '月薪上限65535',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

超出数值类型错误

ERROR 1264 (22003): Out of range value for column 'salary’at row 1

修改已创建表的数值字段为无符号整数,如655635限制

alter table 表名称 modify 列名称 smallint(5) unsigned;

INSERT INTO `tb_user` (`id`, `user_name`, `password`, `name`, `age`, `salary`,`email`) VALUES('1', 'zhangsan', '123456', '张三', '18',65536, 'test1@itcast.cn');