How to store an array into mysql?

How to store an array into mysql?

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Storing Arrays in MySQL: Why and How to Handle List Fields

You are looking to implement a comment rating system where you need to track which users have already voted on a specific comment to prevent duplicate submissions. The proposed architecture involves creating a new table that stores the comment_id alongside an array of user IDs who have voted. You plan to join this voters table with your comments table and check if the current user's ID exists within that stored array to disable voting icons accordingly.

While the logic is sound, storing a native PHP array directly into a MySQL column is technically feasible but generally discouraged for production systems involving relational data integrity. The primary method involves using JSON columns (available in MySQL 5.7+) or utilizing legacy text fields. However, before writing code to serialize your data, you must understand the performance and query limitations inherent to this approach.

Storing Arrays with JSON Columns

If you are running MySQL 5.7 or newer, the standard recommendation is to use the JSON data type. This allows you to store complex nested structures, including arrays, directly in a column.

Consider a table structure designed to hold voter information:

CREATE TABLE comment_votes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    comment_id BIGINT UNSIGNED NOT NULL,
    voters JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_comment_id (comment_id)
);

In this schema, the voters column would contain a JSON array like [123, 456, 789]. You can insert this data using a serialized string if you are using an ORM like Laravel:

$userIds = [101, 102];
$votesTable = new CommentVote();
$votesTable->comment_id = $commentId;
$votesTable->voters = json_encode($userIds); // Stores as JSON string in DB
$votesTable->save();

Querying the Stored Array

Once the data is stored, checking for existence requires specific syntax depending on your MySQL version. If you are using a standard text field or an older MySQL version where JSON functions aren't available, you might rely on string manipulation, which is fragile.

In modern MySQL, you can check for an element in the JSON array using the JSON_CONTAINS function:

SELECT * FROM comment_votes 
WHERE comment_id = 50 
AND JSON_CONTAINS(voters, '123');

This returns a row if user 123 is present in the array. If you need to count how many users have voted or retrieve the full list for the UI, you can use JSON_LENGTH or simply cast the column back to a string/array in your application logic.

The Trade-offs and Pitfalls

The biggest drawback of storing an array in a single column is that it breaks normalization. When a new user votes on a comment, you have two options:

  1. Append to existing array: You must fetch the current row, parse the JSON array, append the new ID, re-serialize it, and write it back. This creates a read-modify-write cycle that can lead to race conditions in high-concurrency environments.
  2. Create a separate row per vote: This is often better if you need to track individual vote timestamps or allow users to unvote (delete their specific entry).

If you stick strictly to your requirement of preventing multiple votes and only storing the list, appending to the array works for low-to-medium traffic. However, as the number of voters grows on a single comment, reading that large JSON blob just to check for one ID becomes less efficient than a set-based lookup.

Normalization is Usually Preferable

If you have the flexibility to change your schema, consider abandoning the array entirely in favor of a normalized relationship. Instead of storing an array of user IDs in one column, create a simple polymorphic or foreign-key based table:

CREATE TABLE comment_votes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    comment_id BIGINT UNSIGNED NOT NULL,
    voter_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_vote (comment_id, voter_id) -- Prevents duplicate votes
);

With this structure, checking if a user has voted is an EXISTS query or a simple JOIN, which database engines handle natively and extremely efficiently:

SELECT 1 FROM comment_votes 
WHERE comment_id = $commentId AND voter_id = $userId;

This approach scales better because the index on (comment_id, voter_id) allows MySQL to instantly locate a specific vote without scanning or parsing JSON blobs. It also makes it trivial to handle unvotes by simply deleting the specific row rather than manipulating a complex array structure.

While storing arrays in JSON columns is convenient for simple prototypes or low-traffic applications, normalization remains the robust choice for systems requiring strict data integrity and performance as user counts increase.

Our network