php - The length of text is not enough. Changing it to mediumtext feels too big. Is there any way?

Question

Newbie question, ridiculous.

I want each article to have an independent access statistics, and the access statistics information has a separate field.

One piece of access data is approximately 450 characters. Almost all are English numbers, only the IP address is in Chinese.

The text length is 65535, which is equivalent to about 145 lines. It feels like too little.
If you change it to mediumtext, it will feel too big and will not be used up, taking up space.

Is there any solution?

Answer 1

For access statistics, you should design a separate table. Setting it as a field is inconvenient to maintain. Let’s get a separate watch

Adjust the field appropriately. Aid is the article ID

Answer 2

mediumtext比text只多1 Byte，如果你觉得这就算太大，用不完，占用空间, then I can’t find a better way

Answer 3

The author first needs to understand that text and longtext are variable-length field types.
This is the description in phpMyAdmin:
text: A text field that can store up to 65535 (2^16-1) bytes, and is stored in Use 2 bytes before the content to indicate the number of bytes of the content.
longtext: A maximum of 4294967295 bytes, or 4GB (2^32-1) text fields can be stored. When storing, use 4 bytes before the content to indicate the number of bytes of the content.
In other words, if you only store 1 character in a longtext field, the space occupied is only 4 bytes plus the number of bytes occupied by 1 character, instead of occupying 4 GB of storage space, 4 GB refers to the maximum value that the field can hold.

Finally, there are multiple quantities that are obviously related and need to be queried and counted. It is not recommended to stuff them all into one field. Things like uncertain session data (such as JSON strings) can be stored in a longtext field. For example, I Conversation table:

DROP TABLE IF EXISTS `io_session`;
CREATE TABLE IF NOT EXISTS `io_session` (
    `user_id` bigint unsigned NOT NULL COMMENT '用户编号',
    `data` longtext NOT NULL COMMENT '会话内容',
    `create_time` bigint unsigned NOT NULL COMMENT '创建时间',
    `update_time` bigint unsigned NOT NULL COMMENT '更新时间',
    `version` smallint unsigned NOT NULL DEFAULT '0' COMMENT '版本号',
    `flag` tinyint NOT NULL DEFAULT '1' COMMENT '版本号递增递减方向标识',
    PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='会话表';

// 填出会话数据到数组 $io['user']['session']
function io_session_get() {
    global $io;
    if(!isset($io['user']['id'])) return false;
    $db = io_db();
    $table = IO_DB_PREFIX.'session';
    $sql = "SELECT * FROM `{$table}` WHERE `user_id` = ?";
    $stmt = $db->prepare($sql); $stmt->execute(array($io['user']['id']));
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if(!isset($rows[0])) return false;
    $data = json_decode($rows[0]['data'], true);
    // JSON解码失败时,把会话内容重置为空
    $io['user']['session']['data']    = ($data) ? $data : array();
    $io['user']['session']['version'] = $rows[0]['version'];
    $io['user']['session']['flag']    = $rows[0]['flag'];
    //register_shutdown_function('io_session_set');
}

// 将会话数据写入到数据库
function io_session_set() {
    global $io;
    if(!isset($io['user']['session'])) return false;
    $data = json_encode($io['user']['session']['data']);
    $db = io_db();
    $table = IO_DB_PREFIX.'session';
    switch(true) {
        // version 类型 smallint 范围 0 到 65535
        case ($io['user']['session']['flag'] == 1 && $io['user']['session']['version'] != 65535): {
            $sql = "UPDATE `{$table}` SET `data` = ?, `version` = ? 
            WHERE `user_id` = ? AND `version` = ? AND `flag` = 1";
            $version_increase = true;
            break;
        }
        case ($io['user']['session']['flag'] == 1 && $io['user']['session']['version'] == 65535): {
            $sql = "UPDATE `{$table}` SET `data` = ?, `version` = ?, `flag` = -1 
            WHERE `user_id` = ? AND `version` = ? AND `flag` = 1";
            $version_increase = false;
            break;
        }
        case ($io['user']['session']['flag'] == -1 && $io['user']['session']['version'] != 0): {
            $sql = "UPDATE `{$table}` SET `data` = ?, `version` = ? 
            WHERE `user_id` = ? AND `version` = ? AND `flag` = -1";
            $version_increase = false;
            break;
        }
        case ($io['user']['session']['flag'] == -1 && $io['user']['session']['version'] == 0): {
            $sql = "UPDATE `{$table}` SET `data` = ?, `version` = ?, `flag` = 1 
            WHERE `user_id` = ? AND `version` = ? AND `flag` = -1";
            $version_increase = true;
            break;
        }
    }
    $stmt = $db->prepare($sql);
    $stmt->execute(array(
        $data,
        $version_increase ? $io['user']['session']['version'] + 1 : $io['user']['session']['version'] - 1,
        $io['user']['id'],
        $io['user']['session']['version'],
    ));
    return ($stmt->rowCount() == 0) ? false : true;
}

io_session_get();
var_export($io['user']['session']);
$io['user']['session']['data']['date'] = date('Y-m-d H:i:s');
io_session_set();