mysql ROW_NUMBER的性能 mysql有row_number函数吗

添加序号

row_number函数简介：
这 row_number()是一个排名函数，它返回一行的序号，从第一行的1开始。

版本低于8.0的MySQL不支持row_number()就像Microsoft SQL Server，Oracle或PostgreSQL一样。幸运的是，MySQL提供了可用于模拟row_number()函数的会话变量 。

MySQL row_number - 为每一行添加一个行号：

要模拟 row_number()函数，您必须在查询中使用会话变量。

以下语句从employees表中获取5名员工， 并从1开始为每行添加行号。

SET @row_number = 0;
SELECT
	( @row_number := @row_number + 1 ) AS num,
	firstName,
	lastName 
FROM
	employees 
	LIMIT 5;

一种技术是使用会话变量作为派生表，并将其与主表交叉连接

SELECT 
    (@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
    employees,(SELECT @row_number:=0) AS t
LIMIT 5;

MySQL row_number - 为每个组添加行号:

SELECT
    customerNumber, paymentDate, amount
FROM
    payments
ORDER BY customerNumber;

假设您为每个客户添加一个行号，并在客户编号更改时重置行号。

要实现此目的，您必须使用两个会话变量，一个用于行号，另一个用于存储旧客户编号，以将其与当前的客户编号进行比较，如下面的查询：

SET @row_number = 0;
SELECT
	@row_number := IF( @customer_no = customerNumber, @row_number + 1, 1 ) AS num,
	@customer_no := customerNumber AS CustomerNumber,
	paymentDate,
	amount 
FROM
	payments 
ORDER BY
	customerNumber;

与 row_number每行一样，您可以使用派生表和交叉连接技术来生成相同的结果。

SELECT 
    @row_number:=CASE
        WHEN @customer_no = customerNumber THEN @row_number + 1
        ELSE 1
    END AS num,
    @customer_no:=customerNumber as CustomerNumber,
    paymentDate,
    amount
FROM
    payments,(SELECT @customer_no:=0,@row_number:=0) as t
ORDER BY customerNumber;

MySQL 随机查询

MySQL使用ORDER BY RAND() 选择随机记录：
MySQL没有任何内置语句来从数据库表中选择随机记录。为了实现这一点，您可以使用RAND功能。以下查询从数据库表中选择一个随机记录：

SELECT 
    *
FROM
    tbl
ORDER BY RAND()
LIMIT 1;

1. RAND()函数为表中的每一行生成一个随机值。
2. 在ORDER BY子句由RAND()函数产生的随机数排序表中的所有行。
3. LIMIT子句选择在随机排序的结果集中的第一行。

如果要从数据库表中选择N个随机记录，则需要更改LIMIT子句，如下所示：

SELECT 
    *
FROM
    table
ORDER BY RAND()
LIMIT N;

例如，要在customers表中选择5个随机客户，请使用以下查询：

SELECT 
    t.customerNumber, t.customerName
FROM
    customers AS t
ORDER BY RAND()
LIMIT 5;

这种技术适用于小表。对于大表，它会非常慢，因为MySQL必须对整个表进行排序以选择随机表。查询的速度还取决于表中的行数。表具有的行越多，为每行生成随机数所花费的时间就越多。

MySQL使用INNER JOIN子句选择随机记录:
此技术要求您要选择随机记录的表具有自动增量 主键字段，并且序列中没有间隙。

以下查询基于主键列生成随机数：

SELECT
	ROUND( RAND( ) * ( SELECT MAX( id ) FROM TABLE ) ) AS id;

我们可以使用上面的查询返回的结果集加入表，如下所示：

SELECT
	t.* 
FROM
	TABLE AS t
	JOIN ( SELECT ROUND( RAND( ) * ( SELECT MAX( id ) FROM TABLE ) ) AS id ) AS x 
WHERE
	t.id >= x.id 
	LIMIT 1;

使用此技术，您必须多次执行查询才能获得多个随机行，因为如果增加限制，查询将只提供从随机选定行开始的连续行。

以下查询从customers表中返回一个随机客户。

SELECT
	t.customerNumber,
	t.customerName 
FROM
	customers AS t
	JOIN ( SELECT ROUND( RAND( ) * ( SELECT MAX( customerNumber ) FROM customers ) ) AS customerNumber ) AS x 
WHERE
	t.customerNumber >= x.customerNumber 
	LIMIT 1;

MySQL使用变量选择随机记录:
如果表中的id列的值在1 … N范围内且范围内没有间隙，则可以使用以下技术：

1. 首先，选择1…N范围内的随机数。
2. 其次，根据随机数选择记录。

以下语句可帮助您完成此任务：

SELECT TABLE.* 
FROM
	(
	SELECT
		ROUND( RAND( ) * ( SELECT MAX( id ) FROM TABLE ) ) random_num,
		@num := @num + 1 
	FROM
		( SELECT @num := 0 ) AS a,
	TABLE 
		LIMIT N 
	) AS b,
	TABLE AS t 
WHERE
	b.random_num = t.id;

请注意，用户定义的变量是特定于连接的。这意味着此技术不能与连接池一起使用。此外，主键必须是整数类型，其值必须在没有间隙的序列中。

在本教程中，我们向您展示了几种从表中选择随机记录的技巧。

MySQL 查询第 N 高记录

要选择第n 个 最高记录，您需要执行以下步骤：

1. 首先，您获得n个最高记录并按升序排序。第n 个 最高记录是结果集中的最后一个记录。
2. 然后按降序对结果集进行排序并获取第一个结果集。

以下是按升序获取前n 个最高记录的查询：

SELECT 
    *
FROM
    table_name
ORDER BY column_name ASC
LIMIT N;

获得第n 个最高记录的查询如下：

SELECT 
    *
FROM
    (SELECT 
        *
    FROM
        table_name
    ORDER BY column_name ASC
    LIMIT N) AS tbl
ORDER BY column_name DESC
LIMIT 1;

幸运的是，MySQL为我们提供了LIMIT子句，子句约束返回结果集中的行数。您可以将以上查询重写为以下查询：

SELECT 
    *
FROM
    table_name
ORDER BY column_name DESC
LIMIT n - 1, 1;

查询返回n-1行后的第一行，因此您获得第n 个最高记录。

获得第二昂贵的产品（n = 2）products，请使用以下查询：

SELECT 
    productCode, productName, buyPrice
FROM
    products
ORDER BY buyPrice DESC
LIMIT 1 , 1; 

-- 或

SELECT 
    productCode, productName, buyPrice
FROM
    products a
WHERE
    1 = (SELECT 
            COUNT(productCode)
        FROM
            products b
        WHERE
            b.buyPrice > a.buyPrice);

MySQL 重置自增值

MySQL重置自动增量值示例：
首先，创建一个名为的tmp表，并将AUTO_INCREMENT属性 分配给 id 主键列。

CREATE TABLE tmp (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(45) DEFAULT NULL,
  PRIMARY KEY (id)
);

其次，将一些示例数据插入tmp 表中：

INSERT INTO tmp(name)
VALUES('test 1'),
      ('test 2'),
      ('test 3');

三，查询tmp 表以确认插入操作：

SELECT 
    *
FROM
    tmp;

查看自动量值，红色标记的位置:

使用ALTER TABLE语句:
可以使用ALTER TABLE语句重置自动增量值。ALTER TABLE 重置自动增量值的语句的语法如下：

ALTER TABLE table_name AUTO_INCREMENT = value;

可以在ALTER TABLE子句后指定表名，并value在表达式中指定要重置的表名AUTO_INCREMENT=value。

请注意，value必须大于或等于自动增量列的当前最大值。

让我们删除tmp 表中最后一条id 值为3的记录：

DELETE FROM tmp 
WHERE
    ID = 3;

如果插入新行，MySQL将为新行的id列分配4 。但是，您可以使用以下ALTER TABLE 语句将MySQL生成的数字重置为3 ：

ALTER TABLE tmp AUTO_INCREMENT = 3;

现在，让我们尝试在tmp 表中插入一个新行并从中查询数据以查看效果：

INSERT INTO tmp(name)
VALUES ('MySQL example 3');
 
SELECT 
    *
FROM
    tmp;

使用TRUNCATE TABLE语句:
TRUNCATE TABLE语句从表中删除所有数据并重置自动递增值为零。

以下说明了TRUNCATE TABLE 语句的语法：

TRUNCATE TABLE table_name;

通过使用TRUNCATE TABLE 语句，可以永久删除表中的所有数据，并将自动增量值重置为零。

使用DROP TABLE和CREATE TABLE语句:
可以使用一对语句：DROP TABLE和CREATE TABLE来重置自动增量列。请注意，此方法会永久删除表中的所有数据。

与TRUNCATE TABLE 语句一样，这些语句会删除表并重新创建它，因此，自动增量的值将重置为零。

DROP TABLE table_name;
CREATE TABLE table_name(...);

可执行的注释

MySQL提供可执行注释以支持不同数据库之间的可移植性。这些注释允许您嵌入仅在MySQL中执行但不在其他数据库中执行的SQL代码。

以下说明了可执行注释语法：

/*! MySQL-specific code */

例如，以下语句使用可执行注释：

SELECT 1 /*! +1 */

语句返回2而不是1.但是，如果在其他数据库系统中执行它，它将返回1。

如果要从特定版本的MySQL执行注释，请使用以下语法：

/*!##### MySQL-specific code */

字符串’#####'表示可以执行注释的MySQL的最低版本。第一个＃是主要版本，例如5或8.第二个2号（##）是次要版本。最后2个是补丁级别。

例如，以下注释仅在MySQL 5.1.10或更高版本中可执行：

CREATE TABLE t1 (
    k INT AUTO_INCREMENT,
    KEY (k)
)  /*!50110 KEY_BLOCK_SIZE=1024; */