MySQL批量数据脚本与函数创建指南

本文主要介绍了如何在MySQL中进行批量数据操作，包括创建数据库表结构、设置服务器参数以及创建自定义函数。重点在于提高数据导入的效率和数据的唯一性。 在MySQL中，批量数据处理通常涉及到数据库表的设计和数据的插入。首先，我们来看两个示例表的创建语句： 1. `dept` 表用于存储部门信息，包含字段 `id`（主键，自动递增），`deptName`（部门名称），`address`（地址）和 `ceo`（首席执行官ID）。表使用 InnoDB 引擎，以支持事务处理，并且设置了默认的 UTF8 字符集。 ```sql CREATE TABLE `dept` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `deptName` VARCHAR(30) DEFAULT NULL, `address` VARCHAR(40) DEFAULT NULL, `ceo` INT NULL , PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; ``` 2. `emp` 表用于存储员工信息，包含字段 `id`（主键，自动递增），`empno`（员工编号，非空），`name`（姓名），`age`（年龄）和 `deptId`（部门ID）。这里还注释掉了一个外键约束，它会将 `deptId` 与 `dept` 表的 `id` 关联起来，以实现数据的一致性。 ```sql CREATE TABLE `emp` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `empno` INT NOT NULL, `name` VARCHAR(20) DEFAULT NULL, `age` INT(3) DEFAULT NULL, `deptId` INT(11) DEFAULT NULL, PRIMARY KEY (`id`) # CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; ``` 在批量数据导入时，可能会遇到因开启二进制日志（`log_bin`）导致的问题。`log_bin_trust_function_creators` 参数用于控制是否信任用户创建的非确定性函数。如果启用了慢查询日志并且 `log_bin` 开关打开，需要设置这个参数为 1，以允许创建函数。 ```sql show variables like 'log_bin_trust_function_creators'; set global log_bin_trust_function_creators = 1; ``` 上述设置仅对当前会话有效，要使其永久生效，需要修改 MySQL 的配置文件： - Windows: 修改 `my.ini` 文件中的 `[mysqld]` 段，添加 `log_bin_trust_function_creators=1`。 - Linux: 修改 `/etc/my.cnf` 文件中的 `[mysqld]` 段，添加 `log_bin_trust_function_creators=1`。 此外，为了确保批量插入的数据具有唯一性，可以创建自定义函数来生成随机字符串。例如，以下是一个生成指定长度随机字符串的函数： ```sql DELIMITER $$ CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) BEGIN DECLARE chars CHAR(50) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; DECLARE str VARCHAR(255) DEFAULT ''; DECLARE i INT DEFAULT 1; WHILE i <= n DO SET str = CONCAT(str, SUBSTRING(chars, FLOOR(RAND() * LENGTH(chars)) + 1, 1)); SET i = i + 1; END WHILE; RETURN str; END$$ DELIMITER ; ``` 这个函数可以根据需要生成指定长度的随机字符串，从而避免数据冲突。 MySQL 批量数据脚本主要涉及数据库表设计、服务器参数配置和自定义函数的创建，这些都是在大规模数据处理中常见的操作。通过合理的表结构设计和适当的功能扩展，可以有效地提高数据导入的效率和数据质量。