mysql创建表时的空值和非空值设置有什么讲究
发布网友
发布时间:2022-04-22 01:58
我来回答
共4个回答
懂视网
时间:2022-04-30 02:04
命名规范
> 库名、表名、字段名必须使用小写字母并采用下划线分割
> 库名、表名、字段名禁止超过32个字符,须见名知意
* 库名、表名、字段名支持最多64个字符,统一规范、易于辨识以及减少传输量不要超过32
> 库名、表名、字段名禁止使用MySQL保留关键字
> 临时库、临时表名必须以tmp为前缀并以日期为后缀
> 备份库、备份表名必须以bak为前缀并以日期为后缀
基本规范
> 使用INNODB存储引擎
* 5.5以后的默认引擘,支持事务,行级锁,更好的恢复性,高并发下性能更好,对多核,大内存,ssd等硬件支持更好
> 表字符集使用UTF8
* 使用utf8字符集,如果是汉字,占3个字节,但ASCII码字符还是1个字节
* 统一,不会有转换产生乱码风险
> 所有表都需要添加注释
> 单表数据量建议控制在5000万以内
> 不在数据库中存储图片、文件等大数据
> 禁止在线上做数据库压力测试
> 禁止从测试、开发环境直连数据库
库表设计规范
> 禁止使用分区表
* MySQL的分区表实际性能不是很好,且管理维护成本较高
> 拆分大字段和访问频率低的字段,分离冷热数据
> 用HASH进行散表,表名后缀使用十进制数,下标从0开始
* 首次分表尽量多的分,避免二次分表,二次分表的难度和成本较高
> 按日期时间分表需符合YYYY[MM][DD][HH]格式
> 采用合适的分库分表策略,如千库十个表、十库百表等
索引设计规范
索引是一把双刃剑,它可以提高查询效率但也会降低插入和更新的速度并占用磁盘空间
> 单张表中索引数量不超过5个
> 单个索引中的字段数不超过5个
> 对字符串使用前缀索引,前缀索引长度不超过10个字符
* 如果有一个CHAR(200) 列,如果在前10个字符内,多数值是惟一的,那么就不要对整个列进行索引。对前10 个字符进行索引能够节省大量索引空间,也可能会使查询更快
> 表必须有主键
> 不使用更新频繁地列作为主键
> 尽量不选择字符串列作为主键
> 不使用UUID、MD5、HASH 作为主键
> 默认使用非空的唯一键
> 主键建议选择自增或发号器
> 重要的SQL必须被索引
* SELECT、UPDATE、DELETE语句的WHERE条件列
* ORDER BY、GROUP BY、DISTINCT的字段
* 多表JOIN的字段
> 区分度最大的字段放在索引前面
> 核心SQL优先考虑覆盖索引
* select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖
> 避免冗余或重复索引
* 合理创建联合索引(避免冗余),index(a,b,c) 相当于 index(a) 、index(a,b) 、index(a,b,c)
> 索引不是越多越好,按实际需要进行创建
* 每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能
> 不在低基数列上建立索引,例如‘性别’
> 不在索引列进行数学运算和函数运算
> 尽量不要使用外键
* 外键用来保护参照完整性,可在业务端实现
* 对父表和子表的操作会相互影响,降低可用性
* INNODB本身对Online DDL的限制
> 不使用%前导的查询,如like “%xxx”
* 无法使用索引
> 不使用反向查询,如 not in / not like
* 无法使用索引,导致全表扫描
* 全表扫描导致buffer pool利用降低
字段设计规范
> 尽可能不要使用TEXT、BLOB类型
* 删除这种值会在数据表中留下很大的"空洞"
* 可以考虑把BLOB或TEXT列分离到单独的表中
> 用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
* 浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的数据范围
浮点数的缺点是会引起精度问题
> 将字符转化为数字
> 使用TINYINT来代替ENUM类型
> 字段长度尽量按实际需要进行分配,不要随意分配一个很大的容量
* the best strategy is to allocate only as much space as you really need
* VARCHAR(N),N表示的是字符数不是字节数,比如VARCHAR(255),可以最大可存储255个汉字,需要根据实际的宽度来选择N
* VARCHAR(N),N尽可能小,因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节,进行排序和创建临时表一类的内存操作时,会使用N的长度申请内存
> 如果可能的话所有字段均定义为not null
> 使用UNSIGNED存储非负整数
* 同样的字节数,存储的数值范围更大。如tinyint 有符号为 -128-127,无符号为0-255
> INT类型固定占用4个字节存储
> 使用TIMESTAMP存储时间
* 因为TIMESTAMP使用4字节,DATETIME使用8个字节, 同时TIMESTAMP具有自动赋值以及自动更新的特性
> 使用INT UNSIGNED 存储IPV4
> 使用VARBINARY存储大小写敏感的变长字符串
> 禁止在数据库中存储明文密码
SQL设计规范
> 使用预编译语句prepared statement
* 只传参数,比传递SQL语句更高效
* 一次解析,多次使用
* 降低SQL注入概率
java 方法如下: protected boolean updateSalary(Connection conn,BigDecimal x,String ID) throws SQLException{ PreparedStatement pstmt = null; try { pstmt = conn.prepareStatement("UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?"); pstmt.setBigDecimal(1, x); pstmt.setString(2, ID); return true; } finally{ if (pstmt!=null){ pstmt.close(); } }
}
> 尽量避免相同语句由于书写格式的不同,而导致多次语法分析
> 避免隐式转换
* 会导致索引失效,如 select userid from table where userid=’1234’
> 充分利用前缀索引
* 必须是最左前缀
* 不可能同时用到两个范围条件
> 避免使用存储过程、触发器、EVENTS等
* 让数据库做最擅长的事
* 降低业务耦合度,为Scale Out、Sharding 留点余地
* 避开BUG
> 避免使用大表的join
* MySQL最擅长的是单表的主键/二级索引查询
* Join消耗较多的内存,产生临时表
> 避免在数据库中进行数学运算
* 容易将业务逻辑和DB耦合在一起
* MySQL不擅长数学运算和逻辑判断
* 无法使用索引
> 减少与数据库的交互次数
* Insert … on duplicate key update
* Replace into 、 insert ignore、insert into values(),(),()…
* Update … where id in (1,2,3,4)
* Alter table tbl_name add column col1, add column col2
> 拒绝大SQL,拆分成小SQL
* 充分利用query cache
* 充分利用多核CPU
> 使用in代替or,in的值不超过1000个
> 禁止使用order by rand()
* 因为ORDER BY rand()会将数据从磁盘中读取,进行排序,会消耗大量的IO和CPU,可以在程序中获取一个rand
值,然后通过在从数据库中获取对应的值
> 使用union all 而不是union
> 程序应有捕获SQL异常的处理机制
> 禁止单条SQL语句同时更新多个表
> 不使用select *
* 消耗cpu和IO、消耗网络带宽
* 无法使用覆盖索引
* 减少表结构变更带来的影响
行为规范
> 批量导入、导出数据必须提前通知DBA协助观察
> 批量更新数据,如update,delete 操作,需要DBA进行审查,并在执行过程中观察服务
> 禁止在从库上执行后台管理和统计类的功能查询
> 禁止有super权限的应用程序账号存在
> 产品出现非数据库导致的故障时及时通知DBA协助排查
> 促销活动或上线新功能必须提前通知DBA进行流量评估
> 数据库数据丢失,及时联系DBA进行恢复
> 对单表的多次alter操作必须合并为一次操作
> 不在MySQL数据库中存放业务逻辑
> 重大项目的数据库方案选型和设计必须提前通知DBA参与
> 对特别重要的库表,提前与DBA沟通确定维护和备份优先级
> 不在业务高峰期批量更新、查询数据库
> 提交线上建表需求,必须详细注明所有相关SQL
MySQL开发规范
标签:数据库规范
热心网友
时间:2022-04-29 23:12
空值是一种特殊的状态, 表示某一个字段"没有被处理过"
几乎在所有的数据库中甚至是编程语言中, 空与非空都有其存在的意义, 举个例子, 一个人员表中, 有一个年龄字段, 这个年龄字段如果为非 空, 则在不知道人员年龄的情况下, 数据无法入库, 否则会显示个奇怪的年龄(比如0岁, 或者-1岁等)
而有些情况泽不可以为空, 最常见的就是"主键", 比如身份证号之类的
热心网友
时间:2022-04-30 00:30
你这样设置之后,是不能插入的空(NULL),而是能插入空串(\\'\\')。 如果不让插入空串,应该使用check,例如: CREATE TABLE table2 ( title varchar(50) NOT NUL
热心网友
时间:2022-04-30 02:05
NULL 对外部程序来说,具体为不知道、不确切的、无法表述的值。所以在很多家公司的开发规范里都明确规定了,必须为 NOT NULL。
其实用到 NULL 的场景都可以转换为有意义的字符或者数值,一是有利用数据的易读性以及后期的易维护性;二是降低 SQL 语句的编写难度。
关于 NULL 的特性如下:
1. 参与 NULL 字段拼接的结果都为 NULL,预期的可能会有差异
预想把字段 r1 做个拼接,再插入到新的表 t3 里,结果 t3 表的记录全为 NULL,跟预期不符。
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`r1` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`r1` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> create table t3 like t1
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t3 select concat(r1,'database') from t1 limit 2;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+------+
| r1 |
+------+
| NULL |
| NULL |
+------+
2 rows in set (0.00 sec)
那正确的方法如下,对 NULL 用函数 IFNULL 特殊处理。
mysql> insert into t3 select concat(ifnull(r1,''),'database') from t1 limit 2;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+----------+
| r1 |
+----------+
| database |
| database |
+----------+
2 rows in set (0.00 sec)
2. 对于包含 NULL 列的求 COUNT 值也不准确
t1 和 t2 的记录数是一样的,但是字段 r1 包含了 NULL,这导致结果忽略了这些值。
mysql> select count(r1) as rc from t1;
+-------+
| rc |
+-------+
| 16384 |
+-------+
1 row in set (0.01 sec)
mysql> select count(r1) as rc from t2;
+-------+
| rc |
+-------+
| 32768 |
+-------+
1 row in set (0.03 sec)
这时候我们可能想到了,正确的方法是用 NULL 相关函数处理,
mysql> select count(ifnull(r1,'')) as rc from t1;
+-------+
| rc |
+-------+
| 32768 |
+-------+
1 row in set (0.03 sec)
或者是直接用 COUNT(*) 包含了所有可能的值
mysql> select count(*) as rc from t1;
+-------+
| rc |
+-------+
| 32768 |
+-------+
1 row in set (0.02 sec)
当然了不仅仅是 COUNT,除了 NULL 相关的函数,大部分对 NULL 处理都不友好。
所以其实从上面两点来看,NULL 的处理都得特殊处理,额外增加了编写 SQL 的难度。
3. 包含 NULL 的索引列
对包含 NULL 列建立索引,比不包含的 NULL 的字段,要多占用一个 BIT 位来存储。
示例
key_len 分别为 43 和 42,t1 比 t2 多了一个字节,那这里可能有人要问了,不是说占了一个 BIT 位吗?那为什么多了一个字节?可以关注我上一篇文章(第02期:MySQL 数据类型的艺术 - 大对象字段)关于 BIT 的详细描述。
mysql> pager grep -i 'key_len'
PAGER set to 'grep -i 'key_len''
mysql> explain select * from t1 where r1 = ''\G
key_len: 43
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t2 where r1 = ''\G
key_len: 42
1 row in set, 1 warning (0.00 sec)
4. 各存储引擎相关的对 NULL 的处理
在 MySQL 8.0 发布后,仅有 InnoDB、MyISAM 和 Memory 支持对包含 NULL 列的索引,其他引擎不支持。比如 NDB。
mysql创建表时的空值和非空值设置有什么讲究
空值是一种特殊的状态, 表示某一个字段"没有被处理过"几乎在所有的数据库中甚至是编程语言中, 空与非空都有其存在的意义, 举个例子, 一个人员表中, 有一个年龄字段, 这个年龄字段如果为非 空, 则在不知道人员年龄的情况下, 数据无法入库, 否则会显示个奇怪的年龄(比如0岁, 或者-1岁等)而有...
MySQL数据表字段为空问题解决方法非空约束限制NULL值mysql不能为...
其中,id和salary字段都设置了非空约束,而age字段则没有设置。如果在插入数据时,没有输入id或salary字段的值,MySQL会自动填写默认值,而如果输入了NULL,则会抛出一个错误。而对于age字段,如果没有输入值,MySQL会将其设为NULL。当然,在创建数据表时,我们也可以使用ALTER TABLE语句,为现有的字段添...
MySQL非空值约束的设置方法mysql不能为空值
1.在创建表时设置非空值约束 在创建表时,可以通过在字段定义中添加NOT NULL关键字来设置该字段为非空值约束。例如,下面的SQL语句创建了一个名为students的表,其中name和age字段都被设置为非空值约束。CREATE TABLE students (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NUL...
MySQL 中NULL和空值的区别是什么?
一是IS NULL 和IS NOT NULL关键字。如果要判断某个字段是否含用空值的数据,需要使用特殊的关键字。其中前者表示这个字段为空,后者表示这个字段为非空。在Select语句的查询条件中这两个关键字非常的有用。如需要查询所有电话号码为空的用户(需要他们补充电话号码信息),就可以在查询条件中加入is not nu...
MYSQL --空值处理
MySQL中空值表示缺失或未定义的值。处理空值时,需理解空值与其他值的关系,并使用特定SQL函数处理。定义NULL值用于表示缺失的值。具有NULL值的字段为空,若字段可选,则记录创建时可留空,字段保存NULL值。NULL值与零值、包含空白不同。比较NULL值需使用特定语法,如IS NULL、IS NOT NULL。NULL值与任何...
MySQL是否必须非空mysql不能为空吗
让我们看看MySQL中的非空约束如何工作。非空约束是一种用于防止在数据库中插入空值的约束。它可以被应用于任何一个表的字段中,这将使该字段只允许非空值的数据被插入。在MySQL中,可以使用NOT NULL关键字来指定一个字段必须是非空的,如下所示:CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR...
MySQL数据库禁止插入空值mysql不能插空值
实际上,MySQL中限制插入空值的方法是非常简单的。在创建MySQL表时,可以通过设置表中列的约束条件来实现禁止插入空值的限制。其中最常用的约束条件是NOT NULL,它可以在定义列时指定,用于防止列接受空值。下面是一个简单的例子:CREATE TABLE Students (id INT NOT NULL,name VARCHAR(50) NOT NULL,age...
避免MySQL插入空值有效提高数据完整性mysql不能插入空值
1. 使用NOT NULL属性 在创建数据库表时,可以使用NOT NULL属性来限制字段的值不能为空。如果尝试插入空值,则会报错。例如:CREATE TABLE users (id INT(11) NOT NULL AUTO_INCREMENT,username VARCHAR(50) NOT NULL,eml VARCHAR(255) NOT NULL,password VARCHAR(255) NOT NULL,PRIMARY KEY (id))...
MySQL不是空探秘MySQL非空约束的应用与实现mysql不是空
一、什么是MySQL的非空约束?MySQL的非空约束,即指当插入一条记录时,该记录中非空字段必须有值,否则会报错,从而保证数据库中的数据不会存在空值。通过使用非空约束,可以有效避免数据的不合法、不完整或不良后果。在MySQL中,可以在创建表或修改表时通过添加“NOT NULL”关键字来设置非空约束。例如...
MySQL主键是否允许为空解密空值主键约束mysql中主键能为空吗
但是,在某些特殊情况下,我们可能需要将主键设置为允许为空值。本文将探讨MySQL主键是否允许为空的问题,并解密空值主键约束的使用。1. 主键的作用及默认约束 MySQL的主键是用来唯一标识每一行数据的,它可以用于连接多个表之间的关系,还可以用于提高查询效率。在创建表时,我们通常会为其中的某一列(或...