我的书签
添加书签
移除书签COLUMNS
COLUMNS 提供了关于表中每列的详细信息。
1. USE INFORMATION_SCHEMA;
2. DESC COLUMNS;
结果如下：
1. +--------------------------+--------+------+------+---------+---------------+
2. | Column | Type | Key | Null | Default | Semantic Type |
3. +--------------------------+--------+------+------+---------+---------------+
4. | table_catalog | String | | NO | | FIELD |
5. | table_schema | String | | NO | | FIELD |
6. | table_name | String | | NO | | FIELD |
7. | column_name | String | | NO | | FIELD |
8. | ordinal_position | Int64 | | NO | | FIELD |
9. | character_maximum_length | Int64 | | YES | | FIELD |
10. | character_octet_length | Int64 | | YES | | FIELD |
11. | numeric_precision | Int64 | | YES | | FIELD |
12. | numeric_scale | Int64 | | YES | | FIELD |
13. | datetime_precision | Int64 | | YES | | FIELD |
14. | character_set_name | String | | YES | | FIELD |
15. | collation_name | String | | YES | | FIELD |
16. | column_key | String | | NO | | FIELD |
17. | extra | String | | NO | | FIELD |
18. | privileges | String | | NO | | FIELD |
19. | generation_expression | String | | NO | | FIELD |
20. | greptime_data_type | String | | NO | | FIELD |
21. | data_type | String | | NO | | FIELD |
22. | semantic_type | String | | NO | | FIELD |
23. | column_default | String | | YES | | FIELD |
24. | is_nullable | String | | NO | | FIELD |
25. | column_type | String | | NO | | FIELD |
26. | column_comment | String | | YES | | FIELD |
27. | srs_id | Int64 | | YES | | FIELD |
28. +--------------------------+--------+------+------+---------+---------------+
29. 24 rows in set (0.00 sec)
创建表 public.t1 并查询其在 COLUMNS 中的信息：
1. CREATE TABLE public.t1 (h STRING, v FLOAT64, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() TIME INDEX, PRIMARY KEY(h));
2. SELECT * FROM COLUMNS WHERE table_schema='public' AND TABLE_NAME='t1'\G
结果如下：
1. *************************** 1. row ***************************
2. table_catalog: greptime
3. table_schema: public
4. table_name: t1
5. column_name: h
6. ordinal_position: 1
7. character_maximum_length: 2147483647
8. character_octet_length: 2147483647
9. numeric_precision: NULL
10. numeric_scale: NULL
11. datetime_precision: NULL
12. character_set_name: utf8
13. collation_name: utf8_bin
14. column_key: PRI
15. extra:
16. privileges: select,insert
17. generation_expression:
18. greptime_data_type: String
19. data_type: string
20. semantic_type: TAG
21. column_default: NULL
22. is_nullable: Yes
23. column_type: string
24. column_comment: NULL
25. srs_id: NULL
26. *************************** 2. row ***************************
27. table_catalog: greptime
28. table_schema: public
29. table_name: t1
30. column_name: v
31. ordinal_position: 2
32. character_maximum_length: NULL
33. character_octet_length: NULL
34. numeric_precision: 22
35. numeric_scale: NULL
36. datetime_precision: NULL
37. character_set_name: NULL
38. collation_name: NULL
39. column_key:
40. extra:
41. privileges: select,insert
42. generation_expression:
43. greptime_data_type: Float64
44. data_type: double
45. semantic_type: FIELD
46. column_default: NULL
47. is_nullable: Yes
48. column_type: double
49. column_comment: NULL
50. srs_id: NULL
51. *************************** 3. row ***************************
52. table_catalog: greptime
53. table_schema: public
54. table_name: t1
55. column_name: ts
56. ordinal_position: 3
57. character_maximum_length: NULL
58. character_octet_length: NULL
59. numeric_precision: NULL
60. numeric_scale: NULL
61. datetime_precision: 3
62. character_set_name: NULL
63. collation_name: NULL
64. column_key: TIME INDEX
65. extra:
66. privileges: select,insert
67. generation_expression:
68. greptime_data_type: TimestampMillisecond
69. data_type: timestamp(3)
70. semantic_type: TIMESTAMP
71. column_default: current_timestamp()
72. is_nullable: No
73. column_type: timestamp(3)
74. column_comment: NULL
75. srs_id: NULL
76. 3 rows in set (0.03 sec)
COLUMNS 表中列的描述如下：
table_catalog：列所属的目录的名称。在 OSS 项目中该值始终为greptime。table_schema：包含列的表所属的数据库的名称。table_name：包含列的表的名称。column_name：列的名称。ordinal_position：列在表中的位置。character_maximum_length：对于字符串列，以字符为单位的最大长度。character_octet_length：对于字符串列，以字节为单位的最大长度。numeric_precision：对于数值数据类型，列的精度。numeric_scale：对于数值数据类型，列的标度。datetime_precision：对于日期时间数据类型，列的小数秒精度。character_set_name：字符串列的字符集的名称。collation_name：字符串列的排序规则的名称。column_key：列的键类型。可以是以下之一：PRI、TIME INDEX或空字符串。extra：关于列的附加信息。privileges：当前用户对该列的权限。generation_expression：对于生成的列，此值显示用于计算列值的表达式。对于非生成的列，该值为空。greptime_data_type：列的 GreptimeDB 数据类型。data_type：列中的数据类型。semantic_type：列的类型。可以是以下之一：TAG、FIELD或TIMESTAMP。column_default：列的默认值。如果默认值被明确设定为NULL，或者列定义中不包含default子句，则该值为NULL。is_nullable：列是否可为空。如果列可以存储空值，则该值为YES；否则，为NO。column_type：列的数据类型。与DATA_TYPE列相同。column_comment：列定义中包含的注释。srs_id：列的空间参考系统（SRS）的 ID。
相应的 SHOW 语句如下：
1. SHOW COLUMNS FROM t1 FROM public;
结果如下：
1. +-------+--------------+------+------------+---------------------+-------+----------------------+
2. | Field | Type | Null | Key | Default | Extra | Greptime_type |
3. +-------+--------------+------+------------+---------------------+-------+----------------------+
4. | h | string | Yes | PRI | NULL | | String |
5. | ts | timestamp(3) | No | TIME INDEX | current_timestamp() | | TimestampMillisecond |
6. | v | double | Yes | | NULL | | Float64 |
7. +-------+--------------+------+------------+---------------------+-------+----------------------+
8. 3 rows in set (0.01 sec)
