mysql动态增添字段怎么实现
短信预约 -IT技能 免费直播动态提醒
今天小编给大家分享的是mysql动态增添字段怎么实现，相信很多人都不太了解，为了让大家更加了解，所以给大家总结了以下内容，一起往下看吧。一定会有所收获的哦。
目录
数据库
mybatis逆向工程
新建springboot项目
遇到的问题
总结
数据库
--用户表CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'E10ADC3949BA59ABBE56E057F20F883E', `propertyId` int(11) NOT NULL DEFAULT -1, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;--属性表CREATE TABLE `property` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) NOT NULL, `key` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;mybatis逆向工程
1.使用idea新建maven项目，pom内容如下：
<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>top.changelife</groupId> <artifactId>mybatis-generator</artifactId> <version>1.0-SNAPSHOT</version> <dependencies> <dependency> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-core</artifactId> <version>1.3.6</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.35</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-maven-plugin</artifactId> <version>1.3.6</version> <configuration> <verbose>true</verbose> <overwrite>true</overwrite> </configuration> </plugin> </plugins> </build></project>2.在class="lazy" data-src/main/resource目录下新建geoneratorConfig.xml文件，内容如下：
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN" "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd"><generatorConfiguration> <classPathEntry location="C:/Users/35152/.m2/repository/mysql/mysql-connector-java/5.1.35/mysql-connector-java-5.1.35.jar"/> <context id="mysqlTables"> <plugin type="org.mybatis.generator.plugins.SerializablePlugin"></plugin> <plugin type="org.mybatis.generator.plugins.EqualsHashCodePlugin"></plugin> <plugin type="org.mybatis.generator.plugins.ToStringPlugin"></plugin> <commentGenerator> <!-- 是否去除自动生成的注释 true：是 ： false:否 --> <property name="suppressAllComments" value="true"/> </commentGenerator> <!--数据库连接的信息：驱动类、连接地址、用户名、密码 --> <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://localhost:3306/test" userId="root" password="123456"> </jdbcConnection> <!-- 默认false，把JDBC DECIMAL 和 NUMERIC 类型解析为 Integer，为 true时把JDBC DECIMAL 和 NUMERIC 类型解析为java.math.BigDecimal --> <javaTypeResolver> <property name="forceBigDecimals" value="false"/> </javaTypeResolver> <!-- targetProject:生成PO类的位置 --> <javaModelGenerator targetPackage="top.changelife.dynamicproperty.model" targetProject="./class="lazy" data-src/main/java"> <!-- enableSubPackages:是否让schema作为包的后缀 --> <property name="enableSubPackages" value="false"/> <!-- 从数据库返回的值被清理前后的空格 --> <property name="trimStrings" value="true"/> </javaModelGenerator> <!-- targetProject:mapper映射文件生成的位置 --> <sqlMapGenerator targetPackage="top.changelife.dynamicproperty.mapper" targetProject="./class="lazy" data-src/main/java"> <!-- enableSubPackages:是否让schema作为包的后缀 --> <property name="enableSubPackages" value="false"/> </sqlMapGenerator> <!-- targetPackage：mapper接口生成的位置 --> <javaClientGenerator type="XMLMAPPER" targetPackage="top.changelife.dynamicproperty.dao" targetProject="./class="lazy" data-src/main/java"> <!-- enableSubPackages:是否让schema作为包的后缀 --> <property name="enableSubPackages" value="false"/> </javaClientGenerator> <!-- 指定数据库表 --> <table tableName="users" domainObjectName="Users" schema="public" enableCountByExample="false" enableDeleteByExample="false" enableUpdateByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table> </context></generatorConfiguration>这里需要重点注意的不是数据库的连接信息的填写，这个用过jdbc的你想必是没有问题的，重点要关注的是classPathEntry，不要以为在pom里面配置了连接mysql的jar包就万事大吉，这里一定要指定你电脑上jar包所在的绝对地址才行。
3.指定运行方式
工具栏Run–>Edit Configurations–>+–>Maven
Command line : mybatis-generator:generate -e
设置完成后点OK，然后就可以运行了。
新建springboot项目
使用idea新建springboot项目 File–>New–>Project–>Spring Initializr……这里比较简单，就不细说了。
在pom.xml中引入相关依赖：
<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>top.changelife</groupId> <artifactId>dynamic-property</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>dynamic-property</name> <description>mysql实现动态属性配置</description> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.0.3.RELEASE</version> <relativePath/> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.35</version> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.0</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build></project>我这里使用mybatis连接数据库，需要在application.properties中进行配置：
spring.datasource.driverClassName=com.mysql.jdbc.Driverspring.datasource.url=jdbc:mysql://localhost:3306/test?username=rootspring.datasource.username=rootspring.datasource.password=1314mybatis.mapper-locations=classpath:mapper/*Mapper.xmlmybatis.config-location=classpath:mapper/config/sqlMapperConfig.xml程序目录结构如下：
下面陆续贴出相关代码，如对springboot和mybatis不甚了解，可查阅相关资料。
sqlMapperConfig.xml
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration> <typeAliases> <typeAlias alias="users" type="top.changelife.dynamicproperty.model.Users"/> <typeAlias alias="property" type="top.changelife.dynamicproperty.model.Property"/> </typeAliases></configuration>PropertyMapper.xml
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="top.changelife.dynamicproperty.dao.PropertyMapper"> <insert id="insert" keyProperty="id" useGeneratedKeys="true" parameterType="java.util.List"> insert into property (uid, property.key,property.value) values <foreach collection="list" item="property" separator=","> (#{property.uid,jdbcType=INTEGER}, #{property.key,jdbcType=VARCHAR}, #{property.value,jdbcType=VARCHAR}) </foreach> </insert></mapper>UsersMapper.xml
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="top.changelife.dynamicproperty.dao.UsersMapper"> <resultMap id="UserResultMap" type="users"> <id property="id" column="id"/> <result column="account" jdbcType="VARCHAR" property="account"/> <result column="password" jdbcType="VARCHAR" property="password"/> <result column="propertyId" jdbcType="INTEGER" property="propertyId"/> <collection property="list" ofType="property"> <id column="property_id" jdbcType="INTEGER" property="id"/> <result column="uid" jdbcType="INTEGER" property="uid"/> <result column="key" jdbcType="VARCHAR" property="key"/> <result column="value" jdbcType="VARCHAR" property="value"/> </collection> </resultMap> <select id="selectAll" resultMap="UserResultMap"> SELECT u.id AS id,u.account AS account,u.password AS PASSWORD,u.propertyId as propertyId, p.id AS property_id,p.uid as uid,p.key AS 'key',p.value AS 'value' FROM users u,property p WHERE u.propertyid = p.uid </select> <insert id="insert" keyProperty="id" useGeneratedKeys="true" parameterType="users"> insert into users (account, password, propertyId) values (#{account,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, #{propertyId,jdbcType=INTEGER}) </insert></mapper>Users
package top.changelife.dynamicproperty.model;import java.io.Serializable;import java.util.List;public class Users implements Serializable { private Integer id; private String account; private String password; private Integer propertyId; private List<Property> list; private static final long serialVersionUID = 1L; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getAccount() { return account; } public void setAccount(String account) { this.account = account == null ? null : account.trim(); } public String getPassword() { return password; } public void setPassword(String password) { this.password = password == null ? null : password.trim(); } public Integer getPropertyId() { return propertyId; } public void setPropertyId(Integer propertyId) { this.propertyId = propertyId; } public List<Property> getList() { return list; } public void setList(List<Property> list) { this.list = list; } @Override public boolean equals(Object that) { if (this == that) { return true; } if (that == null) { return false; } if (getClass() != that.getClass()) { return false; } Users other = (Users) that; return (this.getId() == null ? other.getId() == null : this.getId().equals(other.getId())) && (this.getAccount() == null ? other.getAccount() == null : this.getAccount().equals(other.getAccount())) && (this.getPassword() == null ? other.getPassword() == null : this.getPassword().equals(other.getPassword())) && (this.getPropertyId() == null ? other.getPropertyId() == null : this.getPropertyId().equals(other.getPropertyId())); } @Override public int hashCode() { final int prime = 31; int result = 1; result = prime * result + ((getId() == null) ? 0 : getId().hashCode()); result = prime * result + ((getAccount() == null) ? 0 : getAccount().hashCode()); result = prime * result + ((getPassword() == null) ? 0 : getPassword().hashCode()); result = prime * result + ((getPropertyId() == null) ? 0 : getPropertyId().hashCode()); return result; } @Override public String toString() { StringBuilder sb = new StringBuilder(); sb.append(getClass().getSimpleName()); sb.append(" ["); sb.append("Hash = ").append(hashCode()); sb.append(", id=").append(id); sb.append(", account=").append(account); sb.append(", password=").append(password); sb.append(", propertyid=").append(propertyId); sb.append(", list=").append(list); sb.append(", serialVersionUID=").append(serialVersionUID); sb.append("]"); return sb.toString(); }}Property
package top.changelife.dynamicproperty.model;import java.io.Serializable;public class Property implements Serializable { private Integer id; private Integer uid; private String key; private String value; private static final long serialVersionUID = 1L; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getUid() { return uid; } public void setUid(Integer uid) { this.uid = uid; } public String getKey() { return key; } public void setKey(String key) { this.key = key == null ? null : key.trim(); } public String getValue() { return value; } public void setValue(String value) { this.value = value == null ? null : value.trim(); } @Override public boolean equals(Object that) { if (this == that) { return true; } if (that == null) { return false; } if (getClass() != that.getClass()) { return false; } Property other = (Property) that; return (this.getId() == null ? other.getId() == null : this.getId().equals(other.getId())) && (this.getUid() == null ? other.getUid() == null : this.getUid().equals(other.getUid())) && (this.getKey() == null ? other.getKey() == null : this.getKey().equals(other.getKey())) && (this.getValue() == null ? other.getValue() == null : this.getValue().equals(other.getValue())); } @Override public int hashCode() { final int prime = 31; int result = 1; result = prime * result + ((getId() == null) ? 0 : getId().hashCode()); result = prime * result + ((getUid() == null) ? 0 : getUid().hashCode()); result = prime * result + ((getKey() == null) ? 0 : getKey().hashCode()); result = prime * result + ((getValue() == null) ? 0 : getValue().hashCode()); return result; } @Override public String toString() { StringBuilder sb = new StringBuilder(); sb.append(getClass().getSimpleName()); sb.append(" ["); sb.append("Hash = ").append(hashCode()); sb.append(", id=").append(id); sb.append(", uid=").append(uid); sb.append(", key=").append(key); sb.append(", value=").append(value); sb.append(", serialVersionUID=").append(serialVersionUID); sb.append("]"); return sb.toString(); }}UserController
package top.changelife.dynamicproperty.controller;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.PostMapping;import org.springframework.web.bind.annotation.RequestBody;import org.springframework.web.bind.annotation.RestController;import top.changelife.dynamicproperty.dao.PropertyMapper;import top.changelife.dynamicproperty.dao.UsersMapper;import top.changelife.dynamicproperty.model.Property;import top.changelife.dynamicproperty.model.Users;import java.util.List;@RestControllerpublic class UserController { @Autowired UsersMapper usersMapper; @Autowired PropertyMapper propertyMapper; @GetMapping("/users") public Object selectAllUsers() { return usersMapper.selectAll(); } @PostMapping("/users") public Object insertUsers(@RequestBody Users user) { List<Property> list = user.getList();// System.out.println(list); propertyMapper.insert(list); usersMapper.insert(user); return user; }}代码就这么多，下面启动项目进行测试，我这里使用Postman进行接口测试。
前段可以随意增添list中的属性个数，达到动态增添字段的效果。
这里做得比较简单，实际使用中可以另建一张表，用来存储必备的字段，每次新增的时候都将必备的字段取出来让用户填写，然后其他的再自定义。
遇到的问题
在写这个demo以前，思路是很清晰的，没想到还是遇到不少的问题，首先就是application.properties中配置数据库出错，spring.datasource.username写错了，导致数据库连接获取不到，报错却为Access denied for user ''@'localhost'，找了很久才发现原来是自己粗心导致。
还有就是无论何时，定义了带参数的构造函数，一定要将无参构造函数写上，免得后期出错。
关于mysql动态增添字段怎么实现就分享到这里了，希望以上内容可以对大家有一定的参考价值，可以学以致用。如果喜欢本篇文章，不妨把它分享出去让更多的人看到。
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/12.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
