mysql中关于数据长度的一个‘小测试’

今儿个测试insert方法的时候，发现有点错误：

看来看去我的sql好像没啥问题:
==> Preparing: INSERT INTO user(user_id,username,password,age,gender,phone,QQ,email,portrait,authority,status,integral) VALUE(?,?,?,?,?,?,?,?,?,?,?,?)
==> Parameters: 359522221443518464(Long), 椰子奶糖5(String), 123456(String), 14(Integer), 1(Integer), 13988888888(String), xxxxxxx(String), xxxxxx@qq.com(String), http://www.aliyouth.cn/repository/Image/CodeResource.png(String), 1(Integer), 1(Integer), 0(Long)

看错误信息是我username重复了，其实想说的不是这个（笑），恢复错误场景失败~~

真实的故事是这样的，今天找了个算法叫SnowFlake，用来生成唯一标识的长整型数值的，就像这样：

D:\IDEInstall\jdk1.8\bin\java "-javaagent:D:\软件安装处\IntelliJ_IDEA\IntelliJ IDEA 2017.1.4\lib\idea_rt.jar=19176:D:\软件安装处\IntelliJ_IDEA\IntelliJ IDEA 2017.1.4\bin" -Dfile.encoding=UTF-8 -classpath D:\IDEInstall\jdk1.8\jre\lib\charsets.jar;D:\IDEInstall\jdk1.8\jre\lib\deploy.jar;D:\IDEInstall\jdk1.8\jre\lib\ext\access-bridge-64.jar;D:\IDEInstall\jdk1.8\jre\lib\ext\cldrdata.jar;D:\IDEInstall\jdk1.8\jre\lib\ext\dnsns.jar;D:\IDEInstall\jdk1.8\jre\lib\ext\jaccess.jar;D:\IDEInstall\jdk1.8\jre\lib\ext\jfxrt.jar;D:\IDEInstall\jdk1.8\jre\lib\ext\localedata.jar;D:\IDEInstall\jdk1.8\jre\lib\ext\nashorn.jar;D:\IDEInstall\jdk1.8\jre\lib\ext\sunec.jar;D:\IDEInstall\jdk1.8\jre\lib\ext\sunjce_provider.jar;D:\IDEInstall\jdk1.8\jre\lib\ext\sunmscapi.jar;D:\IDEInstall\jdk1.8\jre\lib\ext\sunpkcs11.jar;D:\IDEInstall\jdk1.8\jre\lib\ext\zipfs.jar;D:\IDEInstall\jdk1.8\jre\lib\javaws.jar;D:\IDEInstall\jdk1.8\jre\lib\jce.jar;D:\IDEInstall\jdk1.8\jre\lib\jfr.jar;D:\IDEInstall\jdk1.8\jre\lib\jfxswt.jar;D:\IDEInstall\jdk1.8\jre\lib\jsse.jar;D:\IDEInstall\jdk1.8\jre\lib\management-agent.jar;D:\IDEInstall\jdk1.8\jre\lib\plugin.jar;D:\IDEInstall\jdk1.8\jre\lib\resources.jar;D:\IDEInstall\jdk1.8\jre\lib\rt.jar;D:\workspace\IntellijIDEA\Home\database\target\classes;D:\IDEInstall\RepMaven\org\springframework\boot\spring-boot-devtools\2.1.7.RELEASE\spring-boot-devtools-2.1.7.RELEASE.jar;D:\IDEInstall\RepMaven\org\springframework\boot\spring-boot\2.1.7.RELEASE\spring-boot-2.1.7.RELEASE.jar;D:\IDEInstall\RepMaven\org\springframework\spring-context\5.1.9.RELEASE\spring-context-5.1.9.RELEASE.jar;D:\IDEInstall\RepMaven\org\springframework\boot\spring-boot-autoconfigure\2.1.7.RELEASE\spring-boot-autoconfigure-2.1.7.RELEASE.jar;D:\IDEInstall\RepMaven\org\springframework\boot\spring-boot-starter-jdbc\2.1.7.RELEASE\spring-boot-starter-jdbc-2.1.7.RELEASE.jar;D:\IDEInstall\RepMaven\org\springframework\boot\spring-boot-starter\2.1.7.RELEASE\spring-boot-starter-2.1.7.RELEASE.jar;D:\IDEInstall\RepMaven\org\springframework\boot\spring-boot-starter-logging\2.1.7.RELEASE\spring-boot-starter-logging-2.1.7.RELEASE.jar;D:\IDEInstall\RepMaven\ch\qos\logback\logback-classic\1.2.3\logback-classic-1.2.3.jar;D:\IDEInstall\RepMaven\ch\qos\logback\logback-core\1.2.3\logback-core-1.2.3.jar;D:\IDEInstall\RepMaven\org\apache\logging\log4j\log4j-to-slf4j\2.11.2\log4j-to-slf4j-2.11.2.jar;D:\IDEInstall\RepMaven\org\apache\logging\log4j\log4j-api\2.11.2\log4j-api-2.11.2.jar;D:\IDEInstall\RepMaven\org\slf4j\jul-to-slf4j\1.7.26\jul-to-slf4j-1.7.26.jar;D:\IDEInstall\RepMaven\javax\annotation\javax.annotation-api\1.3.2\javax.annotation-api-1.3.2.jar;D:\IDEInstall\RepMaven\org\yaml\snakeyaml\1.23\snakeyaml-1.23.jar;D:\IDEInstall\RepMaven\com\zaxxer\HikariCP\3.2.0\HikariCP-3.2.0.jar;D:\IDEInstall\RepMaven\org\slf4j\slf4j-api\1.7.26\slf4j-api-1.7.26.jar;D:\IDEInstall\RepMaven\org\springframework\spring-jdbc\5.1.9.RELEASE\spring-jdbc-5.1.9.RELEASE.jar;D:\IDEInstall\RepMaven\org\springframework\spring-beans\5.1.9.RELEASE\spring-beans-5.1.9.RELEASE.jar;D:\IDEInstall\RepMaven\org\springframework\spring-tx\5.1.9.RELEASE\spring-tx-5.1.9.RELEASE.jar;D:\IDEInstall\RepMaven\org\mybatis\spring\boot\mybatis-spring-boot-starter\2.1.0\mybatis-spring-boot-starter-2.1.0.jar;D:\IDEInstall\RepMaven\org\mybatis\spring\boot\mybatis-spring-boot-autoconfigure\2.1.0\mybatis-spring-boot-autoconfigure-2.1.0.jar;D:\IDEInstall\RepMaven\org\mybatis\mybatis\3.5.2\mybatis-3.5.2.jar;D:\IDEInstall\RepMaven\org\mybatis\mybatis-spring\2.0.2\mybatis-spring-2.0.2.jar;D:\IDEInstall\RepMaven\org\springframework\boot\spring-boot-starter-web\2.1.7.RELEASE\spring-boot-starter-web-2.1.7.RELEASE.jar;D:\IDEInstall\RepMaven\org\springframework\boot\spring-boot-starter-json\2.1.7.RELEASE\spring-boot-starter-json-2.1.7.RELEASE.jar;D:\IDEInstall\RepMaven\com\fasterxml\jackson\core\jackson-databind\2.9.9\jackson-databind-2.9.9.jar;D:\IDEInstall\RepMaven\com\fasterxml\jackson\core\jackson-annotations\2.9.0\jackson-annotations-2.9.0.jar;D:\IDEInstall\RepMaven\com\fasterxml\jackson\core\jackson-core\2.9.9\jackson-core-2.9.9.jar;D:\IDEInstall\RepMaven\com\fasterxml\jackson\datatype\jackson-datatype-jdk8\2.9.9\jackson-datatype-jdk8-2.9.9.jar;D:\IDEInstall\RepMaven\com\fasterxml\jackson\datatype\jackson-datatype-jsr310\2.9.9\jackson-datatype-jsr310-2.9.9.jar;D:\IDEInstall\RepMaven\com\fasterxml\jackson\module\jackson-module-parameter-names\2.9.9\jackson-module-parameter-names-2.9.9.jar;D:\IDEInstall\RepMaven\org\springframework\boot\spring-boot-starter-tomcat\2.1.7.RELEASE\spring-boot-starter-tomcat-2.1.7.RELEASE.jar;D:\IDEInstall\RepMaven\org\apache\tomcat\embed\tomcat-embed-core\9.0.22\tomcat-embed-core-9.0.22.jar;D:\IDEInstall\RepMaven\org\apache\tomcat\embed\tomcat-embed-el\9.0.22\tomcat-embed-el-9.0.22.jar;D:\IDEInstall\RepMaven\org\apache\tomcat\embed\tomcat-embed-websocket\9.0.22\tomcat-embed-websocket-9.0.22.jar;D:\IDEInstall\RepMaven\org\hibernate\validator\hibernate-validator\6.0.17.Final\hibernate-validator-6.0.17.Final.jar;D:\IDEInstall\RepMaven\javax\validation\validation-api\2.0.1.Final\validation-api-2.0.1.Final.jar;D:\IDEInstall\RepMaven\org\jboss\logging\jboss-logging\3.3.2.Final\jboss-logging-3.3.2.Final.jar;D:\IDEInstall\RepMaven\com\fasterxml\classmate\1.4.0\classmate-1.4.0.jar;D:\IDEInstall\RepMaven\org\springframework\spring-web\5.1.9.RELEASE\spring-web-5.1.9.RELEASE.jar;D:\IDEInstall\RepMaven\org\springframework\spring-webmvc\5.1.9.RELEASE\spring-webmvc-5.1.9.RELEASE.jar;D:\IDEInstall\RepMaven\org\springframework\spring-aop\5.1.9.RELEASE\spring-aop-5.1.9.RELEASE.jar;D:\IDEInstall\RepMaven\org\springframework\spring-expression\5.1.9.RELEASE\spring-expression-5.1.9.RELEASE.jar;D:\IDEInstall\RepMaven\net\minidev\json-smart\2.2.1\json-smart-2.2.1.jar;D:\IDEInstall\RepMaven\net\minidev\accessors-smart\1.1\accessors-smart-1.1.jar;D:\IDEInstall\RepMaven\org\ow2\asm\asm\5.0.3\asm-5.0.3.jar;D:\IDEInstall\RepMaven\org\springframework\boot\spring-boot-starter-thymeleaf\2.1.7.RELEASE\spring-boot-starter-thymeleaf-2.1.7.RELEASE.jar;D:\IDEInstall\RepMaven\org\thymeleaf\thymeleaf-spring5\3.0.11.RELEASE\thymeleaf-spring5-3.0.11.RELEASE.jar;D:\IDEInstall\RepMaven\org\thymeleaf\thymeleaf\3.0.11.RELEASE\thymeleaf-3.0.11.RELEASE.jar;D:\IDEInstall\RepMaven\org\attoparser\attoparser\2.0.5.RELEASE\attoparser-2.0.5.RELEASE.jar;D:\IDEInstall\RepMaven\org\unbescape\unbescape\1.1.6.RELEASE\unbescape-1.1.6.RELEASE.jar;D:\IDEInstall\RepMaven\org\thymeleaf\extras\thymeleaf-extras-java8time\3.0.4.RELEASE\thymeleaf-extras-java8time-3.0.4.RELEASE.jar;D:\IDEInstall\RepMaven\com\alibaba\druid\1.1.8\druid-1.1.8.jar;D:\IDEInstall\RepMaven\log4j\log4j\1.2.17\log4j-1.2.17.jar;D:\IDEInstall\RepMaven\mysql\mysql-connector-java\8.0.17\mysql-connector-java-8.0.17.jar;D:\IDEInstall\RepMaven\org\springframework\spring-core\5.1.9.RELEASE\spring-core-5.1.9.RELEASE.jar;D:\IDEInstall\RepMaven\org\springframework\spring-jcl\5.1.9.RELEASE\spring-jcl-5.1.9.RELEASE.jar com.ch.home.utils.SnowFlake
359527394232639488
359527394232639489
359527394232639490
359527394232639491
359527394232639492
359527394232639493
359527394236833792
359527394236833793
359527394236833794
359527394236833795
359527394236833796
359527394236833797
359527394236833798
359527394236833799
359527394236833800
359527394236833801

Process finished with exit code 0

然而这玩意在插入数据库的时候出了问题（表建好了懒得改，就不恢复了），int的范围远远不够这老长的一串数字，

在mysql中int的范围是-2^31 (-2,147,483,648) 到 2^31 – 1 (2,147,483,647) 的整型数据（所有数字），存储大小为4个字节；
所以这是万万不够存的，于是我把int改成了bigint ，其范围是 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字）。存储大小为 8 个字节。
就行了

有趣的是我的数据是这么定义的

理论上还是不能存这么长的数字，这就有些耐人寻味了，猜想应当是mysql自己给他扩容的吧，只要是没超过bigint的极限，应当都可以存，之前int的时候不能存是因为位数远远超过了极限吧~~~