MySQL表无重复值但回显重复，求实现按标签输出播放列表URL

Hey there! Let's break down your two problems one by one, using your apple_music table as a reference.

1. Fixing Unintended Duplicate Values in Query Results #

First up: why you're seeing repeated values even when your table has no duplicate rows. The most common causes here are:

Possible Causes & Fixes #

• Accidental Cartesian Product from Joins: If you're joining your table with another (like a tag-splitting subquery), you might be generating multiple rows per playlist (one for each tag). For example, a playlist with 3 tags would show up 3 times in results. To get unique URLs, add DISTINCT to your query:

  SELECT DISTINCT am.URL
  FROM apple_music am
  -- Your join logic here (e.g., splitting tags)
  WHERE ...;
  

• Application Layer Bug: Double-check your code to make sure it's not looping over results and reprinting rows unnecessarily.
• Hidden Table Duplicates: Confirm there are truly no duplicates in the table by running this check:

  SELECT ID, URL, Tags, COUNT(*)
  FROM apple_music
  GROUP BY ID, URL, Tags
  HAVING COUNT(*) > 1;
  

If this returns any rows, you do have duplicates in the table that need cleaning up.

2. Building a Feature to Fetch URLs by Tag Lists #

Your second goal is to retrieve URLs matching a given set of tags. We have two approaches here—quick fix for your current schema, or a more scalable normalized setup.

Option 1: Quick Fix (Using Your Current Comma-Separated Tags) #

Since your Tags column uses comma-separated values with spaces, we'll need to clean up the string before matching. Use MySQL's FIND_IN_SET function:

• Match any tag in the list:

  SELECT URL
  FROM apple_music
  WHERE FIND_IN_SET('Drake', REPLACE(Tags, ' ', '')) > 0
     OR FIND_IN_SET('Big Sean', REPLACE(Tags, ' ', '')) > 0;
  

  The REPLACE(Tags, ' ', '') removes spaces after commas so FIND_IN_SET works correctly.

• Match all tags in the list:

  SELECT URL
  FROM apple_music
  WHERE FIND_IN_SET('Drake', REPLACE(Tags, ' ', '')) > 0
    AND FIND_IN_SET('Meek Mill', REPLACE(Tags, ' ', '')) > 0;
  

Option 2: Proper Database Normalization (Recommended for Scalability) #

Comma-separated tags are hard to maintain and query efficiently. For a long-term solution, split your data into two tables:

1. Create Normalized Tables:

   -- Stores playlist core data
   CREATE TABLE playlists (
       ID INT PRIMARY KEY,
       URL VARCHAR(255) NOT NULL UNIQUE
   );
   
   -- Links playlists to individual tags (no duplicates per playlist-tag pair)
   CREATE TABLE playlist_tags (
       playlist_id INT,
       tag VARCHAR(50),
       PRIMARY KEY (playlist_id, tag),
       FOREIGN KEY (playlist_id) REFERENCES playlists(ID)
   );
   

2. Insert Your Existing Data:

   -- Add playlists
   INSERT INTO playlists (ID, URL) VALUES
   (1, 'https://ex1.com'),
   (2, 'https://ex2.com'),
   (3, 'https://ex3.com');
   
   -- Add individual tags
   INSERT INTO playlist_tags (playlist_id, tag) VALUES
   (1, 'Drake'), (1, 'Big Sean'), (1, 'Kanye West'),
   (2, 'Lil Pump'), (2, 'The Weeknd'),
   (3, 'Meek Mill'), (3, 'Drake');
   

3. Query with Normalized Tables:

   • Match any tag:

     SELECT DISTINCT p.URL
     FROM playlists p
     JOIN playlist_tags pt ON p.ID = pt.playlist_id
     WHERE pt.tag IN ('Drake', 'Big Sean');
     

   • Match all tags:

     SELECT p.URL
     FROM playlists p
     JOIN playlist_tags pt ON p.ID = pt.playlist_id
     WHERE pt.tag IN ('Drake', 'Meek Mill')
     GROUP BY p.ID, p.URL
     HAVING COUNT(DISTINCT pt.tag) = 2; -- 2 = number of tags in your target list
     

This setup is faster (you can index the tag column) and easier to maintain as your playlist library grows.

内容的提问来源于stack exchange，提问作者evanb629