1.导入表(存在中文)出现ERROR 31600
导入的时候发现存在中文的数据报错
解决方法:
在一开始创建表的时候选择UTF-8
2、创建表时大小写不敏感
mysql在windows环境下对大小写不敏感,即使更改了也会自动变回小写
解决方法:
找到安装目录下的my.int 在mysqld下加入
lower_case_table_names = 2
当然输入0也可以,但是推荐输入2
1是大小写不明敏感
那么问题来了->搜索安装目录没有my.int怎么办?
如果你有my-default.ini 不要侥幸 改那玩意没有用 赶紧回头是岸
解决方法(在你已经配置好环境变量的情况下可以直接用 没有配好的话自己cd到相应的地方再用)
打开命令行输入
sc delete MySql
把mysql服务删掉
然后在自己mysql的安装目录下新建一个my.int
内容输入
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_bin
init_connect='SET NAMES utf8mb4'
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
#下面的basedir和datadir改成你自己的路径
basedir = D:\MySQL
datadir = D:\MySQL\data
port = 3306
# server_id = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 16M
read_rnd_buffer_size = 16M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
然后删除你的mysql目录下的data文件夹,需要的话可以备份
然后输入mysqld --initialize-insecure --user=mysql
来重新创建data
输入mysqld --install "MySql" --defaults-file="d:/mysql/my.ini"
重新创建mysql服务,路径依旧是改成你自己的,然后启动服务就行了
因为一番操作,现在你的密码是空的
输入mysql -u root -p
然后按回车
输入update mysql.user set authentication_string=password("你自己想输入的密码") where user="root";
然后就可以了
不放心的话可以输入输入
mysql -u root -p``自己登陆试试
然后就可以安心快乐地在Navicat创建有大小写的表 也不会自己变回小写了