I am creating a MySQL table and loading data from a local file.
CREATE TABLE `patent` (
`seq_number` mediumint(9) unsigned NOT NULL auto_increment,
`seq_id` varchar(24) default NULL,
`date` date default NULL,
`patent_number` varchar(16) default NULL,
`pat_seq_number` smallint(6) unsigned default '0',
`organism` text,
`assignee` text,
`seq_length` int(8) default '0',
`seq` text,
PRIMARY KEY (`seq_number`),
Index `seq_id` (`seq_id`),
Index `patent_number` (`patent_number`),
Index `pat_seq_number` (`pat_seq_number`),
Index `seq_length` (`seq_length`)
) Engine = MyISAM MAX_ROWS=536870912;
load data local infile '/data/p_table.txt' into table patent ignore 0 lines
(seq_id, date, patent_number, pat_seq_number, organism, assignee, seq, seq_length);
It seems that I am stuck at the number of rows:
mysql> select count(*) from patent;
+----------+
| count(*) |
+----------+
| 16777215 |
+----------+
1 row in set (0.00 sec)
Although my txt data table has about 20 million lines (records).
I tried Engine=InnoDB
I also tried Engine = MyISAM MAX_ROWS=200000000 (200 million)
mysql> show table status like 'patent'\G
*************************** 1. row ***************************
Name: patent
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 16777215
Avg_row_length: 244
Data_length: 4098768552
Max_data_length: 281474976710655
Index_length: 715227136
Data_free: 0
Auto_increment: 16777216
Create_time: 2013-04-14 12:46:10
Update_time: 2013-04-14 12:50:43
Check_time: 2013-04-14 12:54:08
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=536870912
Comment:
1 row in set (0.00 sec)
The manuals and online advices all say I should be able to create large tables.
her is my uanme linux output.
Linux … 2.6.32-279.el6.x86_64 #1 SMP Wed Jun 13 18:24:36 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
Here is the verison for mySQL:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 318
Server version: 5.1.67 Source distribution
How can I create a larger table to increase the number of rows to larger than 2**24=1677215 rows?
Best Answer
The maximum value for your
auto_increment
'seq_number' field is 16777215. You should make this field use anint
or abigint
if you need more that 16,777,215 records.See the MySQL Documentation for info about the
mediumint(9)
format.https://dev.mysql.com/doc/refman/5.0/en/integer-types.html