PHP将MySQL数组转为JSON时如何去除重复值？

Hey there! Let's sort out that duplicate numeric value problem you're hitting when pulling data from MySQL and converting it to JSON via PHP. I’ve run into this a few times, so here are the most common fixes tailored to different scenarios:

1. Fix It at the Database Query Level #

If the duplicates are coming directly from your MySQL results (i.e., identical rows in your table), the cleanest fix is to eliminate them before they even reach PHP. Use the DISTINCT keyword in your SQL query to return only unique rows:

SELECT DISTINCT column1, column2, numeric_column FROM your_table;

Or if you need to group results by specific columns to avoid duplicates, use GROUP BY:

SELECT column1, numeric_column FROM your_table GROUP BY column1, numeric_column;

2. Adjust Your PHP Fetch Mode #

A super common culprit here is using a fetch mode that returns both associative (string-keyed) and numeric (indexed) arrays. For example, PDO’s default PDO::FETCH_BOTH or MySQLi’s default fetch mode will give you duplicate values—once under their column name, once under a numeric index.

Switch to fetching only associative arrays to avoid this:

For PDO: #

$stmt = $pdo->prepare("SELECT * FROM your_table");
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC); // Only string keys here
echo json_encode($data);

For MySQLi: #

$result = mysqli_query($conn, "SELECT * FROM your_table");
$data = mysqli_fetch_all($result, MYSQLI_ASSOC); // Associative only
echo json_encode($data);

This way, your JSON will only have keys matching your column names, no duplicate numeric entries.

3. Clean Up the Array in PHP (If You Already Have It) #

If you can’t adjust the query or fetch mode for some reason, you can filter out the numeric keys from your array before converting to JSON. Use array_map and array_filter to keep only string-keyed entries:

// $originalData is your mixed associative/numeric array from the database
$cleanedData = array_map(function($row) {
    return array_filter($row, function($key) {
        return is_string($key);
    }, ARRAY_FILTER_USE_KEY);
}, $originalData);

echo json_encode($cleanedData);

Pick the method that fits your situation best—most of the time, adjusting the fetch mode or fixing the query will solve it quickly.

内容的提问来源于stack exchange，提问作者Kárpáti András