An interesting question: the maximum length of varchar in MySQL _ MySQL

An interesting question: the maximum length of varchar in MySQL is bitsCN.com.

An interesting question: the maximum length of varchar in MySQL

The minimum heap of 1 K is obviously maintained, but only nine hundred rows of data are output to MySQL.

Check the log and find the following content:

01stderr logs02com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'reqs' at row 103    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3595)04    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)05    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)06    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)07    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)08    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)09    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)10    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333)11    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318)12    at com.successfactors.perflog.hadoop.mapreduce.core.ParseRecordToDB$RecordReducer.insertData(Unknown Source)13    at com.successfactors.perflog.hadoop.mapreduce.core.ParseRecordToDB$RecordReducer.cleanup(Unknown Source)14    at org.apache.hadoop.mapreduce.Reducer.run(Reducer.java:178)15    at org.apache.hadoop.mapred.ReduceTask.runNewReducer(ReduceTask.java:649)16    at org.apache.hadoop.mapred.ReduceTask.run(ReduceTask.java:417)17    at org.apache.hadoop.mapred.Child$4.run(Child.java:255)18    at java.security.AccessController.doPrivileged(Native Method)19    at javax.security.auth.Subject.doAs(Subject.java:415)20    at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1121)21    at org.apache.hadoop.mapred.Child.main(Child.java:249)

It seems that the maximum length of column is not enough. View DDL:

01CREATE TABLE `record` (02  `eid` varchar(100) NOT NULL,03  `module` varchar(32) NOT NULL,04  `stime` int(11) NOT NULL,05  `edate` varchar(32) NOT NULL,06  `fname` varchar(100) NOT NULL,07  `event` varchar(500) NOT NULL,08  `reqs` varchar(10000) NOT NULL,09  PRIMARY KEY (`eid`)10) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It's not enough for me to get 10 thousand. try 65535!

It is found that varchar is directly converted to mediumtext, which is incorrect. Try 22222.

An error is reported again...

Finally, I set it to 20000 in length, but it passes through. through a complex subtraction, we can calculate a more critical value.

I checked it online and found that the maximum column length in MySQL seems to be related to a lot of things. I ran to ask the DBA of the company. it seems that oracle is another mechanism.

The following is an article about the maximum value:

Http://www.bitsCN.com/database/201210/159610.html

01 this is not a fixed number. This article briefly describes the restrictions. 0203 strlen calculates the length of a string. a Chinese character can contain 2 to 0809 mb_strlen characters. based on its character encoding mode, the statistical character quot0607count is used to calculate the number of elements in the array or the number of attributes in the object is.
 "; 16 echo strlen ($ string2); 17 echo"
"; 18 // use the php multi-byte extension function mb_strlen to try 19 echo mb_strlen ($ string1, 'utf8'); 20 echo"
"; 21 echo mb_strlen ($ string2, 'utf8'); 22 echo"
"; 23?> 2425 the output result is: 269273283293301. the restriction rule 3132 contains the following rules when defining a field: 3334a) storage limit 3536 the varchar field stores the actual content separately in addition to the clustered index. the content starts with 1 to 2 bytes to indicate the actual length (when the length exceeds 255, 2 bytes are required ), therefore, the maximum length cannot exceed 65535. 3738b) the encoding length is limited to 3940 characters. if the character type is gbk, each character occupies up to 2 bytes, and the maximum length cannot exceed 32766 characters. if the character type is utf8, each character occupies up to 3 bytes, the maximum length cannot exceed 21845. 4344 for forums with a lot of English, using GBK occupies 2 bytes for each character, while using UTF-8 English occupies only one byte. 4546 if the preceding restrictions are exceeded during definition, the varchar field will be forcibly converted to the text type and generate a warning. 4748c) the length of a row is limited to 4950. In practice, the length of a varchar is limited by the length defined by a row. MySQL requires that the definition length of a row cannot exceed 65535. If the defined table length exceeds this value, the ERROR 5152 ERROR 1118 (42000): Row size too large is displayed. the maximum row size for the used table type, not counting BLOBs, it is 65535. you have to change some columns to TEXT or BLOBs. 53542. computing example 5556 two examples illustrate the calculation of the actual length. 5758a) if a table has only one varchar type, for example, 5960 create table t4 (c varchar (N) charset = gbk; 6162 then the maximum N value here is (65535-1-2)/2 = 32766. 6364 minus 1 is because the actual row storage starts from the second byte '; 6566 minus 2 is because the two bytes in the varchar header indicate the length; the reason for Division 2 of 6768 is that the character encoding is gbk. 69707172b) if a table is defined as 7374 create table t4 (c int, c2 char (30), c3 varchar (N) charset = utf8; 7576 then the maximum value of N here is (65535-1-2-4-30*3)/3 = 218127778 minus 1 and minus 2 is the same as in the previous example; 7980 minus 4 is because c of the int type occupies 4 bytes; 8182 minus 30*3 is because char (30) occupies 90 bytes and the encoding is utf8. 8384 if varchar exceeds the preceding B rule and is forced to be of the text type, each field occupies 11 bytes. of course, this is no longer a "varchar.

BitsCN.com