Skip to main content

16 posts tagged with "mysql"

View All Tags

mysql_error_sqlstate

· One min read

当我们使用jdbc 或者pdo或者其他mysql的驱动的时候，经常看到错误会有两个错误码 举个例子

Error number: 1005; Symbol: ER_CANT_CREATE_TABLE; SQLSTATE: HY000

1005和HY000 或者是

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '34' for key 'PRIMARY',

23000和1062

那么两者的关系是怎么样的呢？

Error code: This value is numeric. It is MySQL-specific and is not portable to other database systems.

SQLSTATE value: This value is a five-character string (for example, '42S02'). SQLSTATE values are taken from ANSI SQL and ODBC and are more standardized than the numeric error codes.

不管怎么样，你会看到两个错误一个是SQLSTATE,一个是 errorcode,两者区别就是SQLSTATE更加标准或者通用一些，而errorcode则是mysql自己的

相关阅读​

mysql 隐式转换

· 7 min read

类型系统​

type and program language 这本书介绍了类型系统. 什么是类型系统呢?

为什么会有隐式转换​

我写了很久弱类型语言,一直遇到各种隐式转换

但是最近(2020/04/26)我大概知道隐式转换的本质了 . 说到底,弱类型语言也是有类型的语言,变量是有类型的.变量就是一个类型集合里面的一个元素

举个例子
比如一个集合颜色{red,yellow,white,blue} 不同类型的变量说到底也不是一个集合的内容,是没法直接比较.那么编译器就帮你做了一个映射,映射成一个类型,然后可以比较了,就那么简单.

那么隐式转换的问题是什么呢?其实是开发人员可能没有注意到发生了隐式转换,执行路径和预期不一致

那么隐式转换的好处是什么呢?可以少写很多代码,可以更快

这本书讲了表达式和求值

sql也是一种弱类型语言,所以也有弱类型的大坑隐式转换 mysql的类型系统有人详细描述过吗?或者有相关的文档来说明吗?就像jls一样,可能是我没有看完完整的mysql文档吧

mysql类型​

mysql类型分为以下几种:

  • numeric
  • date and time
  • string
  • json

例子​

select count(case when number_col='' OR number_col IS NULL THEN 1 END) FROM test;

假如number_col列是数字类型(比如int),则会发生隐式转换 number_col = ''里面,空字符串''会转换成 0

隐式转换在什么时候发生?​

相关sql

select 1='222';
Thread 28 "mysqld" hit Breakpoint 1, my_strtod (str=0x7f3a500061d0 "222", end=0x7f3ad4d46998, error=0x7f3ad4d469bc) at /home/dinosaur/Downloads/mysql-5.7.21/strings/dtoa.c:465
465     {
(gdb) bt
#0  my_strtod (str=0x7f3a500061d0 "222", end=0x7f3ad4d46998, error=0x7f3ad4d469bc) at /home/dinosaur/Downloads/mysql-5.7.21/strings/dtoa.c:465
#1  0x0000000001f7279d in my_strntod_8bit (cs=0x2e8ea60 <my_charset_utf8_general_ci>, str=0x7f3a500061d0 "222", length=3, end=0x7f3ad4d46998, err=0x7f3ad4d469bc)
    at /home/dinosaur/Downloads/mysql-5.7.21/strings/ctype-simple.c:741
#2  0x0000000000fdaaf2 in double_from_string_with_check (cs=0x2e8ea60 <my_charset_utf8_general_ci>, cptr=0x7f3a500061d0 "222", end=0x7f3a500061d3 "")
    at /home/dinosaur/Downloads/mysql-5.7.21/sql/item.cc:3577
#3  0x0000000000fdacc5 in Item_string::val_real (this=0x7f3a500061d8) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item.cc:3594
#4  0x0000000000f9e9b9 in Item::val_result (this=0x7f3a500061d8) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item.h:1592
#5  0x0000000000fedf4b in Item_cache_real::cache_value (this=0x7f3a50006928) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item.cc:10089
#6  0x0000000000fec91a in Item_cache::has_value (this=0x7f3a50006928) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item.cc:9650
#7  0x0000000000fedfbb in Item_cache_real::val_real (this=0x7f3a50006928) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item.cc:10098
#8  0x0000000000fff539 in Arg_comparator::compare_real (this=0x7f3a500065f8) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item_cmpfunc.cc:1748
#9  0x0000000001014cc8 in Arg_comparator::compare (this=0x7f3a500065f8) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item_cmpfunc.h:92
#10 0x00000000010017e7 in Item_func_eq::val_int (this=0x7f3a50006520) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item_cmpfunc.cc:2507
#11 0x0000000000fe6144 in Item::send (this=0x7f3a50006520, protocol=0x7f3a50001d10, buffer=0x7f3ad4d46e10) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item.cc:7563
#12 0x00000000015d4c48 in THD::send_result_set_row (this=0x7f3a50000b70, row_items=0x7f3a500058c8) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_class.cc:4677
#13 0x00000000015ceed3 in Query_result_send::send_data (this=0x7f3a50006770, items=...) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_class.cc:2717
#14 0x00000000015e697a in JOIN::exec (this=0x7f3a500069f0) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_executor.cc:158
#15 0x00000000016892ba in handle_query (thd=0x7f3a50000b70, lex=0x7f3a50002e78, result=0x7f3a50006770, added_options=0, removed_options=0)
    at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_select.cc:184
#16 0x000000000163939e in execute_sqlcom_select (thd=0x7f3a50000b70, all_tables=0x0) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:5156
#17 0x0000000001632405 in mysql_execute_command (thd=0x7f3a50000b70, first_level=true) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:2792
#18 0x000000000163a31c in mysql_parse (thd=0x7f3a50000b70, parser_state=0x7f3ad4d48550) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:5582
#19 0x000000000162f0a3 in dispatch_command (thd=0x7f3a50000b70, com_data=0x7f3ad4d48e00, command=COM_QUERY) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:1458
#20 0x000000000162df32 in do_command (thd=0x7f3a50000b70) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:999
#21 0x0000000001770f97 in handle_connection (arg=0x5271810) at /home/dinosaur/Downloads/mysql-5.7.21/sql/conn_handler/connection_handler_per_thread.cc:300
#22 0x0000000001de0b41 in pfs_spawn_thread (arg=0x526e200) at /home/dinosaur/Downloads/mysql-5.7.21/storage/perfschema/pfs.cc:2190
#23 0x00007f3ade33b6ba in start_thread (arg=0x7f3ad4d49700) at pthread_create.c:333
#24 0x00007f3add76d41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

隐式转换规则​

If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.

If both arguments in a comparison operation are strings, they are compared as strings.

If both arguments are integers, they are compared as integers.

Hexadecimal values are treated as binary strings if not compared to a number.

If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. This is not done for the arguments to IN(). To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.

A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, use CAST() to explicitly convert the subquery value to DATETIME.

If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.

In all other cases, the arguments are compared as floating-point (real) numbers. For example, a comparison of string and numeric operands takes places as a comparison of floating-point numbers.

mysql 隐式转换可能不走索引​

文档只描述了字符串转数字的情况

举例

下面是表的例子先看表的样子,表里面underlying_code 是varchar类型

show create table `base_underlying_information`
CREATE TABLE `base_underlying_information` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `sec_id` varchar(10) NOT NULL COMMENT '标的ID',
  `uni_code` varchar(30) NOT NULL COMMENT '标识，规则code-last-type',
  `underlying_code` varchar(50) NOT NULL COMMENT '标的代码',
  ... 省略一堆其他字段
  PRIMARY KEY (`id`),
  
  KEY `idx_underlying_code` (`underlying_code`),
) ENGINE=InnoDB  CHARSET=utf8 
```
- 隐式转换的时候
当sql中 条件是数字而 `603023`的时候

```
EXPLAIN  SELECT * FROM  `base_underlying_information` WHERE  underlying_code = 603023

```
这时候的explain 是发现没有走索引
因为满足以下条件

> In all other cases, the arguments are compared as floating-point (real) numbers. For example, **a comparison of string and numeric operands takes places as a comparison of floating-point numbers.**
```
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	base_underlying_information	\N	ALL	idx_underlying_code	\N	\N	\N	506079	10.00	Using where
```

发生了隐式转换

下面是文档的描述

> For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If str_col is an indexed string column, the index cannot be used when performing the lookup in the following statement:
```
SELECT * FROM tbl_name WHERE str_col=1;
```
> The reason for this is that there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'.




- 没有隐式转换的时候
因为表里面是varchar 条件里面也是varchar 所以是没有隐式转换  
```
EXPLAIN  SELECT * FROM  `base_underlying_information` WHERE  underlying_code = '603023'
```
```
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	base_underlying_information	\N	ref	idx_underlying_code	idx_underlying_code	152	const	1	100.00	\N
```



看了一下词法分析好像没有做转换的,看了一下词法分析也没有做,那应该是运行时的时候做的,那是哪个函数呢?  

- http://postgres.cn/docs/9.6/extend-type-system.html
- https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html
- https://blog.csdn.net/n88Lpo/article/details/101013055
- https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

mysql explain impossible condition

· One min read

很好奇explain的时候怎么explain到很多内容的,所以遇到了explain的内容是Impossible ON condition的时候觉得很好奇

mysql字符串最大长度

· 5 min read

本文主要是记录mysql各种类型的字符串受什么限制。

前言​

今天遇到一个特别的事情：把一个pdf的文档转成html然后存进mysql里面，所以我用了text 的字段来存。 结果读出来的时候发现少了一截。搜索了一番才发现text居然最大只能支持16kb的字节的内容。

字节和字符​

如果你写过php,你可以比较清晰地知道strlen("你好")和mb_strlen("你好")两者的区别。
如果是java的话，字节流的InputStream 和OutputStream 或者writer和reader这两个系列的区别你肯定也不陌生。

mysql字符串的长度与类型关系​

String Type Storage Requirements

In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.

Data TypeStorage Required
CHAR(M)The compact family of InnoDB row formats optimize storage for variable-length character
BINARY(M)M bytes, 0 <= M <= 255
VARCHAR(M), VARBINARY(M)L + 1 bytes if column values require 0 − 255 bytes, L + 2 bytes if values may require more than 255 bytes
TINYBLOB, TINYTEXTL + 1 bytes, where L < 28
BLOB, TEXTL + 2 bytes, where L < 216
MEDIUMBLOB, MEDIUMTEXTL + 3 bytes, where L < 224
LONGBLOB, LONGTEXTL + 4 bytes, where L < 232
ENUM('value1','value2',...)1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET('value1','value2',...)1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

来源

CHAR​

CHAR 最大是255个字符

用如下的sql创建256个字符的char类型字符串会报错误

ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use BLOB or TEXT instead

CREATE TABLE `test123` ( `name` char(256)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
(gdb) bt
#0  my_error (nr=1074, MyFlags=0) at /home/dinosaur/Downloads/mysql-5.7.21/mysys/my_error.c:194
#1  0x0000000000f93e75 in Create_field::init (this=0x7fb9b8006740, thd=0x7fb9b8000b70, fld_name=0x7fb9b8006730 "name", fld_type=MYSQL_TYPE_STRING, fld_length=0x7fb9b8006738 "256", fld_decimals=0x0, fld_type_modifier=0, 
    fld_default_value=0x0, fld_on_update_value=0x0, fld_comment=0x7fb9b8002fe0, fld_change=0x0, fld_interval_list=0x7fb9b8003150, fld_charset=0x0, fld_geom_type=0, fld_gcol_info=0x0)
    at /home/dinosaur/Downloads/mysql-5.7.21/sql/field.cc:10962
#2  0x000000000163ae21 in add_field_to_list (thd=0x7fb9b8000b70, field_name=0x7fba3d30c460, type=MYSQL_TYPE_STRING, length=0x7fb9b8006738 "256", decimals=0x0, type_modifier=0, default_value=0x0, on_update_value=0x0, 
    comment=0x7fb9b8002fe0, change=0x0, interval_list=0x7fb9b8003150, cs=0x0, uint_geom_type=0, gcol_info=0x0) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:5798
#3  0x000000000178e3f6 in MYSQLparse (YYTHD=0x7fb9b8000b70) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_yacc.yy:6337
#4  0x000000000163d75a in parse_sql (thd=0x7fb9b8000b70, parser_state=0x7fba3d30d550, creation_ctx=0x0) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:7131
#5  0x0000000001639f07 in mysql_parse (thd=0x7fb9b8000b70, parser_state=0x7fba3d30d550) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:5469
#6  0x000000000162f0a3 in dispatch_command (thd=0x7fb9b8000b70, com_data=0x7fba3d30de00, command=COM_QUERY) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:1458
#7  0x000000000162df32 in do_command (thd=0x7fb9b8000b70) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:999
#8  0x0000000001770f97 in handle_connection (arg=0x570d510) at /home/dinosaur/Downloads/mysql-5.7.21/sql/conn_handler/connection_handler_per_thread.cc:300
#9  0x0000000001de0b41 in pfs_spawn_thread (arg=0x5749fc0) at /home/dinosaur/Downloads/mysql-5.7.21/storage/perfschema/pfs.cc:2190
#10 0x00007fba478aa6ba in start_thread (arg=0x7fba3d30e700) at pthread_create.c:333
#11 0x00007fba46d3341d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

varchar最大长度​

和char类似，想创建一个65532字符的varchar类型字段

CREATE TABLE `test123` ( `name` varchar(65533)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

结果也是一样的错误

ERROR 1074 (42000): Column length too big for column 'name' (max = 16383); use BLOB or TEXT instead

(gdb) bt
#0  my_error (nr=1074, MyFlags=0) at /home/dinosaur/Downloads/mysql-5.7.21/mysys/my_error.c:194
#1  0x00000000016c9998 in prepare_blob_field (thd=0x7fb9b8000b70, sql_field=0x7fb9b8006840) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_table.cc:4715
#2  0x00000000016c6a33 in mysql_prepare_create_table (thd=0x7fb9b8000b70, error_schema_name=0x7fb9b8006728 "test", error_table_name=0x7fb9b8006168 "test123", create_info=0x7fba3d30c6b0, alter_info=0x7fba3d30c600, 
    tmp_table=false, db_options=0x7fba3d30b080, file=0x7fb9b8006ac0, key_info_buffer=0x7fba3d30c170, key_count=0x7fba3d30c16c, select_field_count=0) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_table.cc:3721
#3  0x00000000016cac22 in create_table_impl (thd=0x7fb9b8000b70, db=0x7fb9b8006728 "test", table_name=0x7fb9b8006168 "test123", error_table_name=0x7fb9b8006168 "test123", path=0x7fba3d30c180 "./test/test123", 
    create_info=0x7fba3d30c6b0, alter_info=0x7fba3d30c600, internal_tmp_table=false, select_field_count=0, no_ha_table=false, is_trans=0x7fba3d30c3da, key_info=0x7fba3d30c170, key_count=0x7fba3d30c16c)
    at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_table.cc:5131
#4  0x00000000016cb884 in mysql_create_table_no_lock (thd=0x7fb9b8000b70, db=0x7fb9b8006728 "test", table_name=0x7fb9b8006168 "test123", create_info=0x7fba3d30c6b0, alter_info=0x7fba3d30c600, select_field_count=0, 
    is_trans=0x7fba3d30c3da) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_table.cc:5417
#5  0x00000000016cb9a2 in mysql_create_table (thd=0x7fb9b8000b70, create_table=0x7fb9b80061a0, create_info=0x7fba3d30c6b0, alter_info=0x7fba3d30c600) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_table.cc:5463
#6  0x00000000016335be in mysql_execute_command (thd=0x7fb9b8000b70, first_level=true) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:3248
#7  0x000000000163a31c in mysql_parse (thd=0x7fb9b8000b70, parser_state=0x7fba3d30d550) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:5582
#8  0x000000000162f0a3 in dispatch_command (thd=0x7fb9b8000b70, com_data=0x7fba3d30de00, command=COM_QUERY) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:1458
#9  0x000000000162df32 in do_command (thd=0x7fb9b8000b70) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:999
#10 0x0000000001770f97 in handle_connection (arg=0x570d510) at /home/dinosaur/Downloads/mysql-5.7.21/sql/conn_handler/connection_handler_per_thread.cc:300
#11 0x0000000001de0b41 in pfs_spawn_thread (arg=0x5749fc0) at /home/dinosaur/Downloads/mysql-5.7.21/storage/perfschema/pfs.cc:2190
#12 0x00007fba478aa6ba in start_thread (arg=0x7fba3d30e700) at pthread_create.c:333
#13 0x00007fba46d3341d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

(gdb) p sql_field->length 
$2 = 262132
static bool prepare_blob_field(THD *thd, Create_field *sql_field)
{
  DBUG_ENTER("prepare_blob_field");

  if (sql_field->length > MAX_FIELD_VARCHARLENGTH &&      // sql_field->length = 262132
      !(sql_field->flags & BLOB_FLAG))
  {
    /* Convert long VARCHAR columns to TEXT or BLOB */
    char warn_buff[MYSQL_ERRMSG_SIZE];

    if (sql_field->def || thd->is_strict_mode())      // 严格模式下会打印errorERROR 1074 (42000): Column length too big for 
    {                                                   // column 'name' (max = 16383); use BLOB or TEXT instead
      my_error(ER_TOO_BIG_FIELDLENGTH, MYF(0), sql_field->field_name,
               static_cast<ulong>(MAX_FIELD_VARCHARLENGTH /            // MAX_FIELD_VARCHARLENGTH = 65535
                                  sql_field->charset->mbmaxlen));     // sql_field->charset->mbmaxlen = 4
      DBUG_RETURN(1);
    }
    ...
  }

也就是严格模式下，varchar 最大是65535字节的内容，改成varchar(16383)看看

mysql> CREATE TABLE `test123` ( `name` varchar(16383)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.26 sec)

ok,没有问题