朋友发过来一个SQL,,让我执行以下:
CREATE TABLE `ttt` (
`id` DOUBLE ,
`select_type` VARCHAR (57),
`table` VARCHAR (192),
`type` VARCHAR (30),
`possible_keys` VARCHAR (22288),
`key` VARCHAR (192),
`key_len` VARCHAR (22288),
`ref` VARCHAR (3072),
`rows` DOUBLE ,
`Extra` VARCHAR (765)
);
INSERT INTO `ttt` (`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`) VALUES('1','PRIMARY','aaa','index',NULL,'email_2','51',NULL,'5','Using where; Using index');
INSERT INTO `ttt` (`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`) VALUES('2','DEPENDENT SUBQUERY','bbb','ref','id','id','5','test.aaa.id','1','Using where; Using index');
执行第一句建表的时候就卡住了,报错如下:
mysql> CREATE TABLE `ttt` (
-> `id` DOUBLE ,
-> `select_type` VARCHAR (57),
-> `table` VARCHAR (192),
-> `type` VARCHAR (30),
-> `possible_keys` VARCHAR (12288),
-> `key` VARCHAR (192),
-> `key_len` VARCHAR (12288),
-> `ref` VARCHAR (3072),
-> `rows` DOUBLE ,
-> `Extra` VARCHAR (765)
-> );
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql>
看了提示,表的2个varchar字段长度设置过长了,需要改成text,blob之类的类型,修改之后执行成功了:
mysql> use test;
Database changed
mysql>
mysql> CREATE TABLE `ttt` (
-> `id` DOUBLE ,
-> `select_type` VARCHAR (57),
-> `table` VARCHAR (192),
-> `type` VARCHAR (30),
-> `possible_keys` TEXT,
-> `key` VARCHAR (192),
-> `key_len` TEXT,
-> `ref` VARCHAR (3072),
-> `rows` DOUBLE ,
-> `Extra` VARCHAR (765)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `ttt` (`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`) VALUES('1','PRIMARY','aaa','index',NULL,'email_2','51',NULL,'5','Using where; Using index');
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO `ttt` (`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`) VALUES('2','DEPENDENT SUBQUERY','bbb','ref','id','id','5','test.aaa.id','1','Using where; Using index');
Query OK, 1 row affected (0.00 sec)
mysql> select * from ttt;
+------+--------------------+-------+-------+---------------+---------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+-------+-------+---------------+---------+---------+-------------+------+--------------------------+
| 1 | PRIMARY | aaa | index | NULL | email_2 | 51 | NULL | 5 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | bbb | ref | id | id | 5 | test.aaa.id | 1 | Using where; Using index |
+------+--------------------+-------+-------+---------------+---------+---------+-------------+------+--------------------------+
2 rows in set (0.00 sec)
疑惑:varchar(N),这个N不是最大为65535吗?为什么设置成12288就会报错?12288比65535小很多啊。
有问题上官网:http://dev.mysql.com/doc/refman/5.6/en/char.html
In contrast toCHAR
,VARCHAR
values
are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.
原来有N有255这样一个坎啊,超过了N的意义就不一样了。
分享到:
相关推荐
The maximum row size for the used table type, not counting BLOBs, is 65535 向mysql的表插件一个字段 类型为text时,或修改一个字段类型为text时,报出上面的错误。其实我对这个错误的原因理解也不是很深,给...
3、Can’t connect to local MySQL server through socket ‘/Data/mydata/mysql.sock’ socket文件目录不对应导致的问题 4、今天要说的就是 没有打开only_full_group_by Cause:...
博文链接:https://zyx19920203.iteye.com/blog/2301226
MySQL导入sql脚本错误:2006 – MySQL server has gone away 到如一些小脚本很少报错,但最近导入一个10+M的SQL脚本,却重复报错: Error occured at:2014-03-24 11:42:24 Line no.:85 Error Code: 2006 - MySQL ...
Cause com.mysql.jdbc.exceptions.jdbc4.CommunicationsException The last packet successfully received from the server was 47,795,922 milliseconds ago. The last packet sent successfully to the server was...
2017-11-15 19:23:46 1404 [Note] InnoDB: Memory barrier is not used 2017-11-15 19:23:46 1404 [Note] InnoDB: Compressed tables use zlib 1.2.3 2017-11-15 19:23:46 1404 [Note] InnoDB: Not using CPU crc32 ...
Mysql可以正常启动,但innodb的表无法使用 在错误日志里你会看到如下输出: InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes 现在需要做的事情就是把原来的 innodb 的ib_logfile×...
DbLinq.MySql.dll: MySQL support. DbLinq.Oracle.dll: Oracle support, using either the System.Data.OracleClient or Oracle.DataAccess drivers. DbLinq.PostgreSql.dll: PostgreSQL support. DbLinq.Sqlite....
libssl.so.10缺失库文件的解决办法,error while loading shared libraries :libssl.so.10:cannot open shared object file:No such file or directory
Delphi DBX和MySQL连接的发生DBX错误:无法正确初始化驱动。(DBX Error: Driver could not be properly initialized. Client library may be missing, not installed properly, ...),我找到了合适的libmysql.dll和...
MYSQL之 error while loading shared libraries: libtinfo.so.5: cannot open shared objectfile: No such f-附件资源
NULL 博文链接:https://314858770.iteye.com/blog/686136
_mysql.c(42) : fatal error C1083: Cannot open include file: 'config-win.h': No such file or directory error: Microsoft Visual C++ 9.0 is required. Get it from http://aka.ms/vcpython27 希望软件对您...
Python安装MySQL文件,EXE文件 ..._mysql.c(42) : fatal error C1083: Cannot open include file: 'config-win.h': No such file or directory 原来还是驱动问题,不管mysql的事。 希望该安装包对你有所帮助~
2016-03-03T00:02:30.483100Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. 终端不断地重复打印上面的错误日志,从错误日志看起来似乎有...
lnmp安装(nginx mysql php )
error 日志当中的记录: [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist 从发了帖子,只有人看,没有人回复,看到这种情况只能自己解决问题了,自己动手...