MySQL 建表时 AUTOINCREMENT的用法

建表时设定AUTOINCREMENT,若表中字段id中的最大值为9,而创建表的时候AUTOINCREMENT=12,则下次通过程序往表app中insert数据,且没有指定字段id的值时,下次的id将从12开始计算起,而不是10。

比如有些数据原来是写死的,没有记录到数据库中,后来由于需求变化需要做成动态管理,需要保存到数据库中,而原来有部分id已经被使用了,不能用于其它的,此时AUTOINCREMENT就被派上用场了。

如果创建表时不指定AUTOINCREMENT=12,则id为10的值将用于新增记录,将会发生岐义,所以一定要注意。

MySQL on OSX: Library not loaded: libmysqlclient.18.dylib

The error is as below:

import MySQLdb
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "build/bdist.macosx-10.12-intel/egg/MySQLdb/__init__.py", line 19, in <module>
  File "build/bdist.macosx-10.12-intel/egg/_mysql.py", line 7, in <module>
  File "build/bdist.macosx-10.12-intel/egg/_mysql.py", line 6, in __bootstrap__
ImportError: dlopen(/Users/DoraZhang/.python-eggs/MySQL_python-1.2.5-py2.7-macosx-10.12-intel.egg-tmp/_mysql.so, 2): Library not loaded: libmysqlclient.18.dylib
  Referenced from: /Users/DoraZhang/.python-eggs/MySQL_python-1.2.5-py2.7-macosx-10.12-intel.egg-tmp/_mysql.so
  Reason: image not found

The solution is pretty easy; Add the library path in your /.bash_profile or /.profile file:

MYSQL=/usr/local/mysql/bin
export PATH=$PATH:$MYSQL
export DYLD_LIBRARY_PATH=/usr/local/mysql/lib:$DYLD_LIBRARY_PATH

Then source the file.

It worked for me.

MySQL tried to insert Chinese characters into table fields, Error Code: 1366. Incorrect string value

Error Code: 1366. Incorrect string value.

query related character set,

mysql > show variables like ‘%char%’;

+——————————————————–+

| Variablename | Value |

+————————–+——————————————————–+

| charactersetclient | utf8 |

| charactersetconnection | utf8 |

| charactersetdatabase | latin1 |

| charactersetfilesystem | binary |

| charactersetresults | utf8 |

| charactersetserver | latin1 |

| charactersetsystem | utf8 |

| charactersetsdir | /usr/local/mysql-5.6.25-osx10.8-x8664/share/charsets/ |

+————————–

Find out fields’ attributes of the table:

show full columns from tb.orderinfo;

If you want to change the table default character set and all character columns to a new character set, use a statement like this, so the query statement will be:

ALTER TABLE tb.orderinfo CONVERT TO CHARACTER SET utf8;

The collation is converted to ‘utf8’.

mysql explain

type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说,得保证查询至少达到range级别,最好能达到ref。

possible_keys

指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。

key

显示MySQL实际决定使用的键。如果没有索引被选择,键是NULL。

索引的重要性:

https://dev.mysql.com/doc/refman/5.7/en/using-spatial-indexes.html

Screen Shot 2016-01-22 at 11.35.54

 

SELECT id, name FROM food_region where MBRContains(polygons, ST_GeomFromText(‘POINT(116.62437 40.985128)’));

SELECT id,name FROM food_region where MBRContains(polygons, ST_GeomFromText(‘POINT(116.62437 40.985128)’)) and st_contains(polygons, ST_GeomFromText(‘POINT(116.59437 40.985128)’)) = ‘1’;

MBRContains作为索引。

Mysql创建用户错误:ERROR 1364 : Field ‘sslcipher’ doesn’t have a default value解决方法

USE mysql;

INSERT INTO user

(host, user, password,

selectpriv, insertpriv, updatepriv)

VALUES (‘localhost’, ‘dora’,

PASSWORD(‘dora123’), ‘Y’, ‘Y’, ‘Y’);

报错:

Error Code: 1364. Field ‘sslcipher’ doesn’t have a default value

错误原因:

mysql用户表的中某些字段不能为空,没有默认值,其实是操作错误,mysql添加用户不能直接insert。

MySQL版本:5.6.25

正确添加用户方法:

GRANT USAGE ON . TO ‘user01’@’localhost’ IDENTIFIED BY ‘123456’ WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON tb. TO dora@localhost IDENTIFIED BY ‘dora123’;