Dec4

【转】深入理解MySQL字符集和字符序列

Author: laruence  Click: 5428   Comments: 0 Category: 数据库  Tag: mysql

基本概念

• 字符(Character)是指人类语言中最小的表义符号。例如’A'、’B'等;

• 给定一系列字符,对每个字符赋予一个数值,用数值来代表对应的字符,这一数值就是字符的编码(Encoding)。例如,我们给字符’A'赋予数值0,给字符’B'赋予数值1,则0就是字符’A'的编码;

• 给定一系列字符并赋予对应的编码后,所有这些字符和编码对组成的集合就是字符集(Character Set)。例如,给定字符列表为{‘A’,'B’}时,{‘A’=>0, ‘B’=>1}就是一个字符集;

• 字符序(Collation)是指在同一字符集内字符之间的比较规则;

• 确定字符序后,才能在一个字符集上定义什么是等价的字符,以及字符之间的大小关系;

• 每个字符序唯一对应一种字符集,但一个字符集可以对应多种字符序,其中有一个是默认字符序(Default Collation);

• MySQL中的字符序名称遵从命名惯例:以字符序对应的字符集名称开头;以_ci(表示大小写不敏感)、_cs(表示大小写敏感)或_bin(表示按编码值比较)结尾。例如:在字符序“utf8_general_ci”下,字符“a”和“A”是等价的;

MySQL字符集设置

• 系统变量:

character_set_server:默认的内部操作字符集

character_set_client:客户端来源数据使用的字符集

character_set_connection:连接层字符集

character_set_results:查询结果字符集

character_set_database:当前选中数据库的默认字符集

character_set_system:系统元数据(字段名等)字符集

– 还有以collation_开头的同上面对应的变量,用来描述字符序。

• 用introducer指定文本字符串的字符集:

– 格式为:[_charset] ’string’ [COLLATE collation]

– 例如:

• SELECT _latin1 ’string’;

• SELECT _utf8 ‘你好’ COLLATE utf8_general_ci;

– 由introducer修饰的文本字符串在请求过程中不经过多余的转码,直接转换为内部字符集处理。

MySQL中的字符集转换过程

1. MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection;

2. 进行内部操作前将请求数据从character_set_connection转换为内部操作字符集,其确定方法如下:

• 使用每个数据字段的CHARACTER SET设定值;

• 若上述值不存在,则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展,非SQL标准);

• 若上述值不存在,则使用对应数据库的DEFAULT CHARACTER SET设定值;

• 若上述值不存在,则使用character_set_server设定值。

3. 将操作结果从内部操作字符集转换为character_set_results。

常见问题解析

• 向默认字符集为utf8的数据表插入utf8编码的数据前没有设置连接字符集,查询时设置连接字符集为utf8

– 插入时根据MySQL服务器的默认设置,character_set_client、character_set_connection和character_set_results均为latin1;

– 插入操作的数据将经过latin1=>latin1=>utf8的字符集转换过程,这一过程中每个插入的汉字都会从原始的3个字节变成6个字节保存;

– 查询时的结果将经过utf8=>utf8的字符集转换过程,将保存的6个字节原封不动返回,产生乱码……

• 向默认字符集为latin1的数据表插入utf8编码的数据前设置了连接字符集为utf8

– 插入时根据连接字符集设置,character_set_client、character_set_connection和character_set_results均为utf8;

– 插入数据将经过utf8=>utf8=>latin1的字符集转换,若原始数据中含有\u0000~\u00ff范围以外的Unicode字 符,会因为无法在latin1字符集中表示而被转换为“?”(0×3F)符号,以后查询时不管连接字符集设置如何都无法恢复其内容了。

检测字符集问题的一些手段

• SHOW CHARACTER SET;

• SHOW COLLATION;

• SHOW VARIABLES LIKE ‘character%’;

• SHOW VARIABLES LIKE ‘collation%’;

• SQL函数HEX、LENGTH、CHAR_LENGTH

• SQL函数CHARSET、COLLATION

使用MySQL字符集时的建议

• 建立数据库/表和进行数据库操作时尽量显式指出使用的字符集,而不是依赖于MySQL的默认设置,否则MySQL升级时可能带来很大困扰;

• 数据库和连接字符集都使用latin1时虽然大部分情况下都可以解决乱码问题,但缺点是无法以字符为单位来进行SQL操作,一般情况下将数据库和连接字符集都置为utf8是较好的选择;

• 使用mysql C API时,初始化数据库句柄后马上用mysql_options设定MYSQL_SET_CHARSET_NAME属性为utf8,这样就不用显式地用 SET NAMES语句指定连接字符集,且用mysql_ping重连断开的长连接时也会把连接字符集重置为utf8;

• 对于mysql PHP API,一般页面级的PHP程序总运行时间较短,在连接到数据库以后显式用SET NAMES语句设置一次连接字符集即可;但当使用长连接时,请注意保持连接通畅并在断开重连后用SET NAMES语句显式重置连接字符集。

其他注意事项

• my.cnf中的default_character_set设置只影响mysql命令连接服务器时的连接字符集,不会对使用libmysqlclient库的应用程序产生任何作用!

• 对字段进行的SQL函数操作通常都是以内部操作字符集进行的,不受连接字符集设置的影响。

• SQL语句中的裸字符串会受到连接字符集或introducer设置的影响,对于比较之类的操作可能产生完全不同的结果,需要小心!

Nov29

MySQL除法精度问题

Author: leeon  Click: 18578   Comments: 0 Category: 数据库  Tag: mysql

最近在写一个SQL中遇到除法精度问题,比如:

[code="sql"]
SELECT 7185521/7185522
[/code]

得出的结果是1,那么如何让他得到0.999这样的结果呢,后来查google得知可以利用TRUNCATE()函数来解决这样类似大数据除法的精度问题。

  • TRUNCATE(X,D)

返回被舍去至小数点后D位的数字X。若D 的值为 0, 则结果不带有小数点或不带有小数部分。可以将D设为负数,若要截去(归零) X小数点左起第D位开始后面所有低位的值.

[code="sql"]
SELECT TRUNCATE(7185521/7185522,4)
[/code]

例如这样就会得出“0.9999”这样的结果

Nov15

MySQL中删除用户授权的方法

Author: leeon  Click: 9228   Comments: 0 Category: 数据库  Tag: mysql,grant,revke
撤权并删除用户
  要取消一个用户的权限,使用REVOKE语句。REVOKE的语法非常类似于GRANT语句,除了TO用FROM取代并且没有INDETIFED BY和WITH GRANT OPTION子句:

  REVOKE privileges (columns) ON what FROM user

  user部分必须匹配原来 GRANT语句的你想撤权的用户的user部分。privileges部分不需匹配,你可以用GRANT语句授权,然后用REVOKE语句只撤销部分权限。
  REVOKE语句只删除权限,而不删除用户。即使你撤销了所有权限,在user表中的用户记录依然保留,这意味着用户仍然可以连接服务器。要完全删除一个用户,你必须用一条Delete语句明确从user表中删除用户记录:
%mysql -u root mysqlmysql>Delete FROM user ->Where User="user_name" and Host="host_name";mysql>FLUSH PRIVILEGES;

  Delete语句删除用户记录,而FLUSH语句告诉服务器重载授权表。(当你使用GRANT和REVOKE语句时,表自动重载,而你直接修改授权表时不是。)
Oct31

MySQL锁机制/管理(并发锁,行锁,表锁,预加锁,全局锁等等)

Author: mysqlab.net  Click: 6093   Comments: 0 Category: 数据库  Tag: mysql

1. MySQL中并发和隔离控制机制

  • Meta-data元数据锁:在table cache缓存里实现的,为DDL(Data Definition Language)提供隔离操作。一种特别的meta-data元数据类型,叫Name Lock。(SQL层)
  • 表级table-level数据锁(SQL层)
  • 存储引擎特有机制 — row locks行锁,page locks页锁,table locks表级,版本控制(在引擎中实现)
  • 全局读锁 — FLUSH TABLES WITH READ LOCK(SQL层)

2.在语句执行中表的生命周期

DML(Data Manipulation Language):

  • 计算语句使用到的所有表
  • 在每个表:打开open表 — 从table cache缓存里得到TABLE对象,并在此表加上meta-data元数据锁
  • 等待全局读锁后改变数据
  • 在每个表:锁lock表 — 在表加上table-level数据锁
  • 执行语句:调用:handler::write_row()/read_rnd()/read_index(),等;隐式地调用引擎级engine-level锁机制
  • 在每个表:释放表的数据锁
  • 在每个表:释放表的DDL锁并把表放回table cache缓存里
  • DDL语句也是一样,没有典型的执行计划。

3.获取meta-data元数据锁

  • meta-data元数据锁的实现作为TABLE对象的一个属性,TABLE对象代表了table cache缓存。
  • meta-data元数据锁为如下任何一种:
    • shared共享锁 — 隐式地加锁,只通过标记TABLE对象“被使用”;
    • semi-exclusive半独享锁,也叫Name Lock,RENAME操作会在源表和目标加上此锁;
    • exclusive独享,也叫exclusive name lock,CREATE TABLE … SELECT操作会在目标表上加上此锁,如果没有的话。

4.表缓存(table cache)

  • 是一个HASH变量,叫open_cache
  • TABLE对象是HASH元素
  • 以HASH的操作被LOCK_open mutex互斥量保护

4.1内部结构(The table cache: internal structure)

  • 在缓存里,每个物理表可能被多个TABLE实例表示
  • 相同表的所有TABLE实例,通过相连的列(a linked list)连接着
  • 每个TABLE实例有一个table cache缓存版本的复制 — TABLE实例保存的版本不会和当前table cache缓存版本一致,而是保存旧的和从缓存删除的
  • 被某些语句使用的TABLE实例被会标记为对其它的语句来说是无效的 — 这就是meta-data元数据锁的本质
  • 在缓存中的TABLE实例通常地有一个有效的句柄实例连接着它

4.2内部运算(The table cache: operations)

  • 主要的代码在:sql/sql_base.cc,sql/lock.cc,sql/table.h,sql/sql_table.cc
  • 主要的方法:open_table(),close_thread_tables(),close_cached_table(),lock_table_names()
  • 事实上,一个概念/对象组合不仅用于缓存或锁定:LOCK_open mutex互斥量也用到其它的操作,如:使磁盘上和处理中的表创建的原子性
  • 典型的操作,来自隔离等级Pov的重要(注:isolation PoV没研究出是什么意思):语句查询时,打开和关闭表 — shared共享锁;强制和等待直到表的所有实例被关闭 —  exclusive独享(但不完全);Name Lock — 特殊地情况,当手上没有TABLE实例,只能使用一个特殊的占位符(甚至表可能不存在)。

4.4锁多表(The table cache: locking multiple tables)

  • 使用一种尝试和回退(try and back-off)的技术来避免死锁(乐观锁)
  • 为了DDL操作的一套诀窍,如使锁升级或者防止DDL失效
  • LOCK_open问题
  • Lock_open互斥量:
  • 保护table cache缓存内的结构
  • 分组存储引擎内的表和对象的.frm文件的创建,也为RENAME操作提供原子性操作
  • 在每个语句访问表时会使用它两次:在open_tables()和close_thread_tables()
  • 在使用DDL操作时,磁盘读写和甚至同步(sync)都会使用它

5.ALTER TABLE例子

ALTER TABLE执行的简化计划:

  • 以TL_WRITE_ALLOW_READ的打开和加锁表(新版 InnoDB Plugin已改为:TL-READ-NO-INSERT)
  • 创建一个以临时名字的被ALTER的复制表
  • 强制并等待直到表的所有实例都关闭(锁升级)
  • 交换新和旧的版本
  • 删除旧的版本

这是一般情况,当然还有优化的情况。

A debug trace for ALTER TABLE

  1. [email protected]: | query: alter table t1 add column k int
  2. [email protected]: | >mysql_parse
  3. [email protected]: | | >mysql_execute_command
  4. [email protected]: | | | >mysql_alter_table
  5. [email protected]: | | | | >open_ltable
  6. [email protected]: | | | | | >open_table
  7. [email protected]: | | | | | <open_table
  8. [email protected]: | | | | | >mysql_lock_tables
  9. [email protected]: | | | | | | >get_lock_data
  10. [email protected]: | | | | | | | >ha_innobase::store_lock
  11. [email protected]: | | | | | | | <ha_innobase::store_lock
  12. [email protected]: | | | | | | <get_lock_data
  13. [email protected]: | | | | | | >lock_external
  14. [email protected]: | | | | | | | >ha_innobase::external_lock
  15. [email protected]: | | | | | | | | enter: lock_type: 1
  16. [email protected]: | | | | | | | | >trans_register_ha
  17. [email protected]: | | | | | | | | | enter: stmt
  18. [email protected]: | | | | | | | | <trans_register_ha
  19. [email protected]: | | | | | | | <ha_innobase::external_lock
  20. [email protected]: | | | | | | <lock_external
  21. [email protected]: | | | | | | >thr_multi_lock
  22. [email protected]: | | | | | | | >thr_lock
  23. [email protected]: | | | | | | | <thr_lock
  24. [email protected]: | | | | | | <thr_multi_lock
  25. [email protected]: | | | | | <mysql_lock_tables
  26. [email protected]: | | | | <open_ltable
  27. [email protected]: | | | | >mysql_create_table
  28. [email protected]: | | | | <mysql_create_table
  29. [email protected]: | | | | >open_temporary_table
  30. [email protected]: | | | | | >openfrm
  31. [email protected]: | | | | | | >handler::ha_open
  32. [email protected]: | | | | | | | enter: name: ./test/#sql-3081_1 db_type: 12 db_stat: 7 mode: 2 lock_test: 2
  33. [email protected]: | | | | | | | >ha_innobase::open
  34. [email protected]: | | | | | | | <ha_innobase::open
  35. [email protected]: | | | | | | <handler::ha_open
  36. [email protected]: | | | | | <openfrm
  37. [email protected]: | | | | <open_temporary_table
  38. [email protected]: | | | | >copy_data_between_tables
  39. [email protected]: | | | | <copy_data_between_tables
  40. [email protected]: | | | | >closefrm
  41. [email protected]: | | | | <closefrm
  42. [email protected]: | | | | >close_cached_table
  43. [email protected]: | | | | | enter: table: t1
  44. [email protected]: | | | | | >wait_while_table_is_used
  45. [email protected]: | | | | | | >get_lock_data
  46. [email protected]: | | | | | | <get_lock_data
  47. [email protected]: | | | | | | >thr_abort_locks
  48. [email protected]: | | | | | | <thr_abort_locks
  49. [email protected]: | | | | | | >remove_table_from_cache
  50. [email protected]: | | | | | | | enter: Table: ‘test.t1′ flags: 2
  51. [email protected]: | | | | | | <remove_table_from_cache
  52. [email protected]: | | | | | <wait_while_table_is_used
  53. [email protected]: | | | | | >mysql_unlock_tables
  54. [email protected]: | | | | | | >thr_multi_unlock
  55. [email protected]: | | | | | | | lock: data: 0x8b7f9b0 count: 1
  56. [email protected]: | | | | | | | >thr_unlock
  57. [email protected]: | | | | | | | <thr_unlock
  58. [email protected]: | | | | | | <thr_multi_unlock
  59. [email protected]: | | | | | | >unlock_external
  60. [email protected]: | | | | | | | >ha_innobase::external_lock
  61. [email protected]: | | | | | | | <ha_innobase::external_lock
  62. [email protected]: | | | | | | <unlock_external
  63. [email protected]: | | | | | <mysql_unlock_tables
  64. [email protected]: | | | | | >unlink_open_table
  65. [email protected]: | | | | | | >hash_delete
  66. [email protected]: | | | | | | | >free_cache_entry
  67. [email protected]: | | | | | | | | >closefrm
  68. [email protected]: | | | | | | | | | >ha_innobase::close
  69. [email protected]: | | | | | | | | | <ha_innobase::close
  70. [email protected]: | | | | | | | | <closefrm
  71. [email protected]: | | | | | | | <free_cache_entry
  72. [email protected]: | | | | | | <hash_delete
  73. [email protected]: | | | | | <unlink_open_table
  74. [email protected]: | | | | <close_cached_table
  75. [email protected]: | | | | >mysql_rename_table
  76. [email protected]: | | | | | >ha_innobase::rename_table
  77. [email protected]: | | | | | <ha_innobase::rename_table
  78. [email protected]: | | | | <mysql_rename_table
  79. [email protected]: | | | | >mysql_rename_table
  80. [email protected]: | | | | | >ha_innobase::rename_table
  81. [email protected]: | | | | | <ha_innobase::rename_table
  82. [email protected]: | | | | <mysql_rename_table
  83. [email protected]: | | | | >my_delete
  84. [email protected]: | | | | | my: name ./test/#sql2-3081-1.frm MyFlags 0
  85. [email protected]: | | | | <my_delete
  86. [email protected]: | | | | >ha_delete_table
  87. [email protected]: | | | | | >ha_innobase::delete_table
  88. [email protected]: | | | | | <ha_innobase::delete_table
  89. [email protected]: | | | | <ha_delete_table
  90. [email protected]: | | | | >ha_commit_trans [email protected]: | | | | <ha_commit_trans [email protected]: | | | | >ha_commit_trans [email protected]: | | | | <ha_commit_trans [email protected]: | | | <mysql_alter_table
  91. [email protected]: | | <mysql_execute_command
  92. [email protected]: | <mysql_parse
  93. [email protected]: <dispatch_command

6.RENAME TABLE例子

  • 得到源表和目的表的name-lock锁:在table cache缓存内插入特殊的TABLE实例的占位符并等待直到这些表的所有实例都关闭
  • 重命名这些表的.frm文件和调用handler::rename_table()方法
  • 删除name-lock锁

在整个解析过程中,都使用LOCK_open

Simplified debug trace for RENAME TABLE

  1. [email protected]: | query: rename table t1 to t2
  2. [email protected]: | >mysql_parse
  3. [email protected]: | | >mysql_execute_command
  4. [email protected]: | | | >mysql_rename_tables
  5. [email protected]: | | | | >lock_table_names
  6. [email protected]: | | | | | >lock_table_name
  7. [email protected]: | | | | | | enter: db: test name: t1
  8. [email protected]: | | | | | <lock_table_name
  9. [email protected]: | | | | | >remove_table_from_cache
  10. [email protected]: | | | | | | enter: Table: ‘test.t1′ flags: 0
  11. [email protected]: | | | | | | >hash_delete
  12. [email protected]: | | | | | | | >free_cache_entry
  13. [email protected]: | | | | | | | | >closefrm
  14. [email protected]: | | | | | | | | | >ha_innobase::close
  15. [email protected]: | | | | | | | | | <ha_innobase::close
  16. [email protected]: | | | | | | | | <closefrm
  17. [email protected]: | | | | | | | <free_cache_entry
  18. [email protected]: | | | | | | <hash_delete
  19. [email protected]: | | | | | <remove_table_from_cache
  20. [email protected]: | | | | | >lock_table_name
  21. [email protected]: | | | | | | enter: db: test name: t2
  22. [email protected]: | | | | | <lock_table_name
  23. [email protected]: | | | | | >remove_table_from_cache
  24. [email protected]: | | | | | | enter: Table: ‘test.t2′ flags: 0
  25. [email protected]: | | | | | <remove_table_from_cache
  26. [email protected]: | | | | <lock_table_names
  27. [email protected]: | | | | >rename_tables
  28. [email protected]: | | | | | >do_rename
  29. [email protected]: | | | | | | >mysql_rename_table
  30. [email protected]: | | | | | | | >ha_innobase::rename_table
  31. [email protected]: | | | | | | | <ha_innobase::rename_table
  32. [email protected]: | | | | | | | >my_rename
  33. [email protected]: | | | | | | | | my: from ./test/t1.frm to ./test/t2.frm MyFlags 16
  34. [email protected]: | | | | | | | <my_rename
  35. [email protected]: | | | | | | <mysql_rename_table
  36. [email protected]: | | | | | <do_rename
  37. [email protected]: | | | | <rename_tables
  38. [email protected]: | | | | >unlock_table_names
  39. [email protected]: | | | | | >unlock_table_name
  40. [email protected]: | | | | | | >hash_delete
  41. [email protected]: | | | | | | | >free_cache_entry
  42. [email protected]: | | | | | | | <free_cache_entry
  43. [email protected]: | | | | | | <hash_delete
  44. [email protected]: | | | | | <unlock_table_name
  45. [email protected]: | | | | | >unlock_table_name
  46. [email protected]: | | | | | | >hash_delete
  47. [email protected]: | | | | | | | >free_cache_entry
  48. [email protected]: | | | | | | | <free_cache_entry
  49. [email protected]: | | | | | | <hash_delete
  50. [email protected]: | | | | | <unlock_table_name
  51. [email protected]: | | | | <unlock_table_names
  52. [email protected]: | | | <mysql_rename_tables
  53. [email protected]: | | <mysql_execute_command
  54. [email protected]: | <mysql_parse

7.表级table-level锁

  • 主要源代码见:sql/lock.cc,mysys/thr_lock.cc。mysql_lock/unlock_tables()(SQL层操作)和thr_multi_lock()/thr_lock()(锁兼容逻辑lock-compatibility logic)
  • 表是以打开着被加锁的。被加锁的对象被句柄关联着;存储引擎会调整锁的类型。如innodb/bdb,事实上大量的对象被加锁的,如merge/partition,见handler::store_lock()方法。
  • 使用锁等级避免死锁。所有表一次性加锁;如果存储引擎调整锁造成死锁,由存储引擎负责
  • 在一些情况下,表会更早地被解锁

8 .预加锁(pre-locking)

  • 历史上避免死锁方案用于表级table-level数据锁,是要求一次性加锁一个语句内的所有表
  • 因此,对语句使用的函数/触发,我们不得不打开所有直接地或间接地用到的表,且对它们加锁。为这个,我们建立一个被使用表的可传送闭包
  • 为了有效实现,我们混合层次和访问(layers and access)成某些解析/语句上下文(parser/statement context),这些上下文来自主要处理表的模板

9.全局读锁(global read lock)

  • 实现为FLUSH TABLES WITH READ LOCK,用来备份
  • 从执行上防止DDL和DML
  • 建议:每个DDL/DML语句检查是否有一个正挂着的全局读锁和停止是否有任何一个。
    • 通过直接调用wait_if_global_read_lock()(在这个情况我们会设置来自全局读锁的保护,且只有调用start_waiting_global_read_lock()来消除这个保护,通常在这情况下没有打开的表);
    • 或者通过mysql_lock_tables()(在后一种情况下,我们还重新打开表)
  • 线程操作FLUSH TABLES WITH READ LOCK来设置一个全局读锁的标识,初始一个FLUSH TABLES语句,然后等待直到所有的表缓存都清空

分类

标签

归档

最新评论

Abyss在00:04:28评论了
Linux中ramdisk,tmpfs,ramfs的介绍与性能测试
shallwe99在10:21:17评论了
【原创】如何在微信小程序开发中正确的使用vant ui组件
默一在09:04:53评论了
Berkeley DB 由浅入深【转自架构师杨建】
Memory在14:09:22评论了
【原创】最佳PHP框架选择(phalcon,yaf,laravel,thinkphp,yii)
leo在17:57:04评论了
shell中使用while循环ssh的注意事项

我看过的书

链接

其他

访问本站种子 本站平均热度:7502 c° 本站链接数:29 个 本站标签数:460 个 本站被评论次数:94 次