mysql问题汇总——持续更新

1.this is incompatible with sql_mode=only_full_group_by

set @@sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

2.表“TableDetails”中列“IsPrimaryKey”的值为 DBNull

set global optimizer_switch='derived_merge=off';
set optimizer_switch='derived_merge=off';
如果执行到此处仍然无效,重启mysql再次执行即可,多次亲测有效

3.navicat设置外键保存后消失

设置数据库引擎为InnoDB

4.MySQL8.0 Authentication method 'caching_sha2_password' not supported by any of the available plugins.

修改默认的配置文件
C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
default_authentication_plugin=mysql_native_password

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAmYAAAAmCAYAAABu61fPAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAAzJSURBVHhe7Z3dldu6DoVdlwtyHbcEv6WKeXUBKSIvruCsFOGLzR+JpEiAEqWxPNnfWT4ZiyJAgABI0c7k8t9//71A/JMQQgghhLyHC/7HzRkhhBBCyPvhiRkhhBBCyEngiRkhhBBCyEngiRkhhBBCyEkwT8zuv26vx9/76/r1CFcIIYQQQsgRGCdmj9ft1/31/HN7XX8/wzWb5+/r6/K/S+V1fd3/zu23P6HD4Ygd0C+2rOXxldpy3W/M4tMod41vz8D3z1+bx9d7/fdu/U2+Kb5Oa78Cxhxr0c9ge32rcab8/ul8Yv6Mwviy0U/MwkkZHLnGibg/BhsCL/a9/5qL4VqZ4/hN5ipg/6/b68i0SX1V4k4rw8/vQNP/HfN3Zvs/BS2+zs6R/k9r0c9gQ31T+P76/PM4e/04enyafMaXTvPEzD9VFq8NH2emG7MUNzG/ZeMTZZeT+BeFZtZ9/bqv2yBl/REEReEy5FftT/v/uefyf8+jh224Hu2OsmoLZHXhxIYw6kxeqwJ5ZHwd+kfmL+q/ft3q/XvsN0+EnmKX1+NeMne3dDFW/LOX/nui/yr6owbTfoO7G7fY8jvvX9to1OLLnH9HMn8Ym+jK7lHsH7WvL/7b/u3BbczSGMD4Uv8N1J8+/xbjl59hw4Sl36pvCj5+8vFM9S7IgA2H5XcXRv6W/kvm39LfNT5z/pXxDdaPPv8l40NbmZ8aR+dXh/yR+OpjLL/SnIVv3TjDGKdcCe3pXE57hAH5QD8xE4Wuoxi19ekSA8gn3OODTwYS5EJHGlR3CcpsImCoGNcHnDLLxiS5JEompks+AiydzIzn65n4pLTTBV6RiKl9EdxXuw7GnmjGx2c98YzMn+ufzFHZH/TY3/If5N3+JNehH5uZJCY0/4AR/YsTGZeoxeJl2K+BxdUle2JPbbyt8eG6Nv9+4xLe/4XsUIQKNPkj9gHN/5Z/LdzmBPdHGa7/rG+s/tj+9X57zPEg7f31KYw1mXv4N+2vgrpWeciGjOhTN38H57cG7tfydzS/rHbLPmt8YKR+mOPrzE+NI/MLaPKdfYet/9GvW/MrgD4yp+5TQ4xT7on1YjFvyO8kp0bl698xgzJRDqdlk7QCbWPWLFyNHbebSH+HTiorkm6yeuVrGzNpy3bE8nNqj2pfwmKCE3oSu8kO47MSa2T+RvVH6v7zRUTF8A/YrB/2dyx8lv0ai8IpLGQKdf8Y+jH+0n+N8W2S30nT/x3+tVD91xG/Frb9iNFZNr4yERcpU3/Nl7U5U4BvIWHaoMp/5cZGjQ9tfMLY/Bv52zH/lv4x+zrqi7C9fnWMr9S/yr+eI/MLaPaP+b+HgfyKqD5Fvsybr6ye7CC/eWKGDdVCcGWyLDZtzJxT+4vMgprBWTB3ys/6pEh/PB0lT0ylPbp9M7ivNTk9iV1nn/FtTqwO/47qj9T95/WXV2ds/4DN+hE33JgN2Qea/u/wr4XuPzt+LVbZ/1e2RfgoatJp6K/JataqOt5XXo/7GR8rJT7Vx2/7Z2z+vfzm3Tvk15h9xvgC2+uXMb4V+alxZH4Bzf4x/69kbX5FDJ9ONuC+zF/j8tUTsxg0tSLWy7aNmQ8CHEVuA46RoJjGvDzq75LfKnbuuuyWYxEI31VJ7Ym+cz9LO3bQtUlI7ytJ/Y7AusqOu35nwU7j0/SPzp/VH/TY3/If4i69/nSLUPBBh3/AiP5FzoRFMN7ZY7/GHh9lxuu1+c/Gv/GjzBH7gOZ/y78W5UeZzgeJ/8bqT+6Xln/Tj8Lc33xPiruu365vJqLv9hXmBLrdeEKbYM3fHvmtoeavMJpfo/ZZ4wNa/EbSOEmxxpfZv/mjzOPyC2jyR/1vAd3b8ytQjGmJ98nCV8KofPU7Zn5TJcorAWUiSvMTt7noYVLidTc5yb3zQEOxCdcvYjwc2z2OkCi+PyYJdkBOLF66fFe4Y1t8JROb2oAF8uHep8mS6EcRlcB09zr7wlgWr3xhdcEU27JCbDM2Pk9L/+j89fVv6+/znxSrQn95Qhbbqv4R9tSPDf66+NdxxcB94TvoXjs+c/6X7VnbJDN9efl72Af0+G/71wJ1zc112DC15O9Wfyr+xSb6lslfqd+sbwZ4OJni/Sl959j/rvzW0fO3bF+TX6P2eazxyR3N+N0jf5bx1e9bz1H5FWnJ38f/OmP5VZ8fN9YCZ4v0WzImXz0xc0/gSOCqYkLIW7hc/MYsFhp5n3HAe2xcp6I5Ko8QsitZfpKPx1VMbXNGCDkXOM3FZgdPWdOm54A/Sz2u7Ffu2/QnIWSI9FMd97WGcJ18PsZv/ieEnJZyk/Np7wkhhCxwlZKbM0IIIYSQ98MTM0IIIYSQk8ATM0IIIYSQk8ATM0IIIYSQk8ATM0IIIYSQk9B5YvZ43S6X1+W64jdLBx636+uCvu51fd32+pVoj9sk93r/rL8o/Lx7n+zmiwEet/f67936m3xTfJ3W/lE+JD9/rP9Pxfb1g5B/kRUnZpJcaxPreX9drxv+1YAVYJPTKqx30f3OvY+mH+M+emN2Zvs/BS2+iM07/fcT4u/nsGH9IOQfpX1i9kQi+Sdef9JVJFbWLk+dt/wX3OFJNLZNr7T/457Lv88ltDxRirJqBb5a+LEhjDqT16qN0Mj4OvRDxu2e3FcuIop/o/7r7Vbv32O/eaLxlFuS006Zu9v1+ppuVfyzl/57ov8q+mN3036Duxu32HLP+9eGUYsvc/4dyfxhbKIru0exf8w+0Rvu9/qln+hycqKMaX7m+Yw65xwt/C8/Yw5mpF3iwbeH+pD199T8Z9Fl/4Hxl9Yu15bcO9ln1D+NrvjT7HN0zE+j3evP7Z5snmS08w+kMY/YzuILWOsHIaRJ48QMSSRJNuVtWKSTxLpL0cwKERJRilMGCmQzGZ+vZyIAib7YuBSFtFbgtcI/9sQ8Pj7rxMwtmqEL/Jv2t/zr+idzVPYHPfa3/Ad5tzkAvH4sJtMl3T9gRD8W/eyyK/TF5tSwXwOLk1tMpy7YaCzH2xofrmvz78YfBydKnL6GnNb1pn3iawme+guDchsT77+n2xB6OX5OvYhyvHJnZr8f1/xv0z2xOUnzP5ufZX2ItOyzUO13HBt/i+vwV6Kgq/4p2PFn1x9tftR2xEfpLAE+jiZb+eeATIkzyHJjlXv8mHHvPHfS4GTX4oMQsqR+YoYiVBardJPVeCJ1C4C/w6NtzKQte+KUn8vCk9WO2piEVmEFPYW5yQ7j0/Sr/Tv8O6o/UvefL8Iqhn/AZv2wvxQmpAtHj/0ai4VHWMgU6v4x9GP8pf8a49sk3yLVL/0uQVAus9gILOQjBtL5lXtjc82+2jWhZZ+Fab/oOyz+HE+RP2++sniBrUFv/rL1Rcz4M+1T5seht8M3eOtOz2TeZBso94eNF+wrnSmk+edoxWTteiM+CCFL5BG7cmJmJlbHwg2aySj98USfVIqyEJuFOdAurH2Fuc4+49P06/1t/47qj9T95/WXV2ds/4DN+jsWhh77NT56Y9ZzYhb1S7/6xkyYZNZPCyeesmzj5C3KrNnXyPWWfRaq/UfHX2CSCd35YPrqn4Ief332TZTzU1Jp97nk7XA/42sVUUFH/jmyOUmoXW/EByFkSeM7ZkhYKWpTbi2PovEeR+UqrWR01+WJLch/hu9TpLUgLZhoxxNqrQhohTUtfihMV3mird9ZsNP4NP2LQlsUM8u/Vn/QY3/Lf/joJL3+dEU8+KDDP2BE/2LhKj5K6bFfY4+PMuP12vxn4xclWz7K3GxfmnfSr7kxCza77zoVsjH+9KNsfBSWLtb5/BzzUWbT/m+IP4+PuUUsCl31T0GNvw77euZHa4c/b/gOHwxDm7s/tAkLmxsfZdZ9Z68fhJA28ohdOTEDYSH2R+FIWnmPn6fkCsnm2nFdCpVkdsxFd0Qe2+IryXwUxHgdBerh3qeLWaIfSS6F1d3rbghjWbzyhdUVo9iWFQqbsfF5WvpT2c4lcl98P/dv+7evf1t/n/9koSj0l0/wsa3qH2FP/dhoxLZe+zXcwuO+kBx0rx2fOf/L9qxtkpm+vPwx+xLZ6Bz6ol+U62RGXHtiVwCbhFs2/3JPphobidge6sNUG3T7LHrsPz7+PE5P5rCIXv8s9Piz7bPmx5w/bP4meU/xR+47XGt/+b/uv8xN5vpBCGnB3/xP/kkWJwIHg4XV3lS9AWxedhkXFmIuur18d/wRQj6H9okZIT+U7DT3wM1EqgenHmdah/FR9eSDxWnJOjJZ3JyZfFf8EUI+E56YEUIIIYSchH/vxMx9tyJ5Yl28lt8zIR8E5/fc/PT5YfwRQgbhiRkhhBBCyEngd8wIIYQQQk4CT8wIIYQQQk4CT8wIIYQQQk7B6/V/iyx5knIrSXwAAAAASUVORK5CYIIA" alt="" />

修改密码并重启服务
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; #修改加密规则
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; #更新一下用户的密码
FLUSH PRIVILEGES; #刷新权限

5.变量赋值

用户变量,在客户端链接到数据库实例整个过程中用户变量都是有效的。

MySQL中用户变量不用事前申明,在用的时候直接用“@变量名”使用就可以了。 

第一种用法:set @num=; 或set @num:=; //这里要使用set语句创建并初始化变量,直接使用@num变量 

第二种用法:select @num:=; 或 select @num:=字段名 from 表名 where ……, 

select语句一般用来输出用户变量,比如select @变量名,用于输出数据源不是表格的数据。

注意上面两种赋值符号,使用set时可以用“=”或“:=”,但是使用select时必须用“:=赋值”

 6.MYSQL5.7版本sql_mode=only_full_group_by问题

、关闭only_full_group_by 模式
、使用any_value(field)允许非分组字段出现
select count(*) as num,any_value(a.name)
上一篇:RV32FD指令集


下一篇:Linux常用命令——持续更新(2018-05-09)