Mysql_01_mysql方法合集

1. 秒级别时间戳

select unix_timestamp(now());

2.当前时间戳

select current_timestamp();

3. 毫秒级时间戳：

REPLACE(unix_timestamp(current_timestamp(3)),'.','');

4.删除一张表重复记录只保留一条

DELETE a
FROM
    test1 a,
    (
        SELECT
            c. SUBJECT,
            c.RECEIVER,
            max(c.id) AS bid
        FROM
            test1 c
        WHERE
            STATUS = 0
        GROUP BY
            RECEIVER,
            SUBJECT
        HAVING
            count(1) > 1
    ) b
WHERE
    a. SUBJECT = b. SUBJECT
AND a.RECEIVER = b.RECEIVER
AND a.id < b.bid;

案例：
DELETE a
FROM
    s_community a,
    (
        SELECT
            c.name,
            max(c.community_id) AS bid
        FROM
            s_community c
        GROUP BY
            name
        HAVING
            count(1) > 1
    ) b
WHERE
 a.name = b.name
AND a.community_id < b.bid;

 5.存储过程INSERT INTO

CREATE PROCEDURE test_fi4()
BEGIN
DECLARE temp INT DEFAULT 10063;
WHILE temp > 0 DO
 INSERT INTO extract_prize_info
VALUES
 (
  NULL,
  1111111111,
  '',
  1,
  '',
  '2021-02-01 10:57:33',
  '2021-02-25 10:57:36',
  NULL,
  NULL,
  '0'
 );
SET temp = temp - 1;
END WHILE;
END;

 6.stream sum ave

//BigDecimal:
BigDecimal bb =list.stream().map(Plan::getAmount).reduce(BigDecimal.ZERO,BigDecimal::add);

//int、double、long:
double max = list.stream().mapToDouble(User::getHeight).sum();