donglin9068 2011-05-03 18:51
浏览 164
已采纳

当达到表大小限制时,MYSQL是否会截断表?

I have been pouring over the MYSQL documentation trying to determine if my script or MYSQL itself is causing a truncation of a large table. The basic behavior I am seeing:

  • A PHP script is kicked off from the browser to select rows from a MSSQL table and immediately insert them into a local MySQL database table.
  • After determining the number of rows in MSSQL, my script breaks down the select into chunks to avoid any out of memory issues.
  • The script then truncates the local table and starts to import the data by iterating over the chunks
  • Following the data in MySQL directly, I see the table close in on ~200,000 rows and then suddenly the table truncates and the count starts over
  • I would think that I would see some errors about the table being full and I didn't think MySQL would truncate the table if the table limit was reached.

Can anyone confirm whether or not MySQL would truncate a table automatically if the table limit is reached or a setting to check to turn off such behavior?

  • 写回答

2条回答 默认 最新

  • douerqu2319 2011-05-03 18:56
    关注

    No MySQL sets no hard limits on the size of the table, but there are some limits...

    Explicit table size
    You can set the max table size with

    ALTER TABLE tbl_name MAX_ROWS=200000
    

    You can see the current settings for table size with

    SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';
    

    See: http://dev.mysql.com/doc/refman/5.0/en/full-table.html

    Note that MAX_ROWS is:

    The maximum number of rows you plan to store in the table. This is not a hard limit, but rather a hint to the storage engine that the table must be able to store at least this many rows.

    Filesystem limits
    MySQL 5.x has a default tablesize of 256TB, but if your filesystem does not allow files > 4GB, that might be a limit that can cause this behaviour as well.

    Memory limits
    If you're using the memory (heap) engine, your table size is determined by the max_heap_table_size system variable.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥50 树莓派安卓APK系统签名
  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗