mysql删除重复行

插入数据，其中“橘子”和“苹果”各有一个重复行

<span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> `products` <span style="color: #0000ff;">VALUES</span> (<span style="color: #800000; font-weight: bold;">5</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">橘子</span><span style="color: #ff0000;">‘</span>, <span style="color: #800000; font-weight: bold;">100.00</span><span style="color: #000000;">);
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> `products` <span style="color: #0000ff;">VALUES</span> (<span style="color: #800000; font-weight: bold;">6</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">橘子</span><span style="color: #ff0000;">‘</span>, <span style="color: #800000; font-weight: bold;">100.00</span><span style="color: #000000;">);
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> `products` <span style="color: #0000ff;">VALUES</span> (<span style="color: #800000; font-weight: bold;">7</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">苹果</span><span style="color: #ff0000;">‘</span>, <span style="color: #800000; font-weight: bold;">50.00</span><span style="color: #000000;">);
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> `products` <span style="color: #0000ff;">VALUES</span> (<span style="color: #800000; font-weight: bold;">8</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">苹果</span><span style="color: #ff0000;">‘</span>, <span style="color: #800000; font-weight: bold;">50.00</span><span style="color: #000000;">);
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> `products` <span style="color: #0000ff;">VALUES</span> (<span style="color: #800000; font-weight: bold;">9</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">香蕉</span><span style="color: #ff0000;">‘</span>, <span style="color: #800000; font-weight: bold;">80.00</span>);

删除重复行

<span style="color: #0000ff;">DELETE</span> 
    <span style="color: #0000ff;">FROM</span><span style="color: #000000;">
        products p1 
    </span><span style="color: #0000ff;">WHERE</span><span style="color: #000000;">
        p1.id </span><span style="color: #808080;"><</span><span style="color: #000000;"> (
        </span><span style="color: #ff0000;"><strong>SELECT
            max_id 
        FROM
            ( SELECT max( p2.id ) max_id FROM products p2 WHERE p1.NAME = p2.NAME AND p1.price = p2.price ) AS</strong></span><span style="color: #000000;"><span style="color: #ff0000;"><strong> a</strong> </span>
        )</span>

注意红色高亮部分写法，由于mysql不支持在自查询中执行update操作，需select 的结果再通过一个中间表select一次。否则会出现如下错误

You can‘t specify target table ‘p1‘ for update in FROM clause

mysql删除重复行

标签：products   date   price   values   HERE   spec   name   innodb   style