MySQL中dd::columns表结构转Table过程以及应用

一、MySQL的dd表介绍

MySQL的dd表是用来存放表结构和各种建表信息的，客户端建的表都存在mysql.table和mysql.columns表里，还有一个表mysql.column_type_elements比较特殊，用来存放SET和ENUM类型的字段集合值信息。看一下下面这张表的mysql.columns表和mysql.column_type_elements信息。为了缩短显示长度，这里只展示几个重要的值。

#建表：
CREATE TABLE t1(id int  not null auto_increment primary key,col1 number,col2 VARCHAR(100),col3 pls_integer,
col4 enum('x','y') default 'x',col5 set('x1','y1'))  partition by hash(id) partitions 3;
SET SESSION debug='+d,skip_dd_table_access_check';
mysql> select name,ordinal_position,type,default_value_utf8,options,column_type_utf8 from mysql.columns where table_id=383;
+-------------+------------------+-----------------------+--------------------+-------------------+------------------+
| name        | ordinal_position | type                  | default_value_utf8 | options           | column_type_utf8 |
+-------------+------------------+-----------------------+--------------------+-------------------+------------------+
| col1        |                2 | MYSQL_TYPE_NEWDECIMAL | NULL               | interval_count=0; | decimal(65,0)    |
| col2        |                3 | MYSQL_TYPE_VARCHAR    | NULL               | interval_count=0; | varchar(100)     |
| col3        |                4 | MYSQL_TYPE_LONG       | NULL               | interval_count=0; | int              |
| col4        |                5 | MYSQL_TYPE_ENUM       | x                  | interval_count=2; | enum('x','y')    |
| col5        |                6 | MYSQL_TYPE_SET        | NULL               | interval_count=2; | set('x1','y1')   |
| DB_ROLL_PTR |                8 | MYSQL_TYPE_LONGLONG   | NULL               | NULL              |                  |
| DB_TRX_ID   |                7 | MYSQL_TYPE_INT24      | NULL               | NULL              |                  |
| id          |                1 | MYSQL_TYPE_LONG       | NULL               | interval_count=0; | int              |
+-------------+------------------+-----------------------+--------------------+-------------------+------------------+
8 rows in set (0.00 sec)

mysql.columns表说明如下：

ordinal_position是该字段在表里的偏移量，这里多了3个字段，DB_ROLL_PTR、DB_TRX_ID、id是用来执行undo的，记录了字段的版本信息。

default_value_utf8是用来保存默认值的。options里面有interval_count用来保存集合类型的数值数的。columns表的options的key一共有如下几种：

static const std::set default_valid_option_keys = {
    "column_format", "geom_type",         "interval_count", "not_secondary",
    "storage",       "treat_bit_as_char", "zip_dict_id",    "is_array"};

mysql>  select * from mysql.column_type_elements where column_id=4286;
+-----------+---------------+------+
| column_id | element_index | name |
+-----------+---------------+------+
|      4286 |             1 | x    |
|      4286 |             2 | y    |
+-----------+---------------+------+
2 rows in set (0.01 sec)
#这里的column_id=4286是col4的id值，x和y分别对应了set定义时候的2个集合值。

二、代码跟踪

现在重新启动数据库，跟踪一下这个columns表怎么转为代码里面的TABLE的field对象。首先找到表的dd信息然后打开表获取field信息。

mysql> select * from t1;

输入该命令后找到columns表转为field的代码：

#0  fill_column_from_dd (
    thd=0x555558b35a06 <std::char_traits<char>::compare(char const*, char const*, unsigned long)+61>, 
    share=0x7fffe83f1b60, 
    col_obj=0x555558bb0a5e <std::__cxx11::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> >::compare(std::__cxx11::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const&) const+142>, 
    null_pos=0x7fffe83f1880 "05", null_bit_pos=32767, rec_pos=0x7fff2c05ac10 "explicit_encryption", 
    field_nr=0) at /mysql/sql/dd_table_share.cc:955
#1  0x00005555593c4c17 
                        来源：https://www.51cto.com/article/719231.html