mysql 替换序列_方便MySQL查询：随机行，序列空白，替换内容和字数统计

mysql 替换序列

Occasionally you’ll have a need for a query that doesn’t quite fall under the standard range of MySQL statements. A few examples:

有时，您需要的查询范围不会完全超出MySQL语句的标准范围。 一些例子：

随机行 (Random Rows)

SELECT * FROM your_table ORDER RAND() LIMIT 1

The query selects a single random row; more rows can be added by raising the integer at the end. This query is only usable if the database table remains relatively small: above 10,000 rows or so, it doesn’t scale efficiently.

查询选择一个随机行； 通过在末尾增加整数可以添加更多行。 仅当数据库表相对较小时才可使用此查询：超过10,000行左右时，它无法有效扩展。

查找编号序列中的缺口 (Finding gaps in numbered sequences)

Useful if you’ve generated rows with an auto-numbered index that has been thrown out of sequence by the deletions of rows.

如果您生成的行带有自动编号的索引，而该索引已被删除行弄乱了顺序，则很有用。

SELECT a.number + 1 FROM your_table AS a WHERE NOT EXISTS 
	(SELECT b.number FROM your_table AS b WHERE a.number + 1 = b.number) 
	GROUP BY a.number;

This will deliver missed numbers for a particular field, allowing you to backfill them with new articles and retain a complete auto-numbered sequence. The next entry number, after the most recent entry, will also be included.

这将为特定字段提供遗漏的编号，使您可以用新文章回填它们并保留完整的自动编号序列。 最近输入之后的下一个输入编号也将包括在内。

在MySQL中查找和替换 (Find and replace in MySQL)

Very useful for databases of all kinds, but perhaps most particularly CMS’s, when updating content or references that have been made across multiple entries. Be sure to make a backup of your database tables before running a query such as this; the ability to change multiple rows at the same moment should be used very carefully.

当更新跨多个条目进行的内容或引用时，对于所有类型的数据库(尤其是CMS )非常有用。 在运行诸如此类的查询之前，请确保对数据库表进行备份。 应该非常小心地使用同时更改多行的功能。

UPDATE your_table SET a_field = REPLACE(a_field,'string_to_find', 'string_to_replace');

字数 (Word Count)

SELECT SUM( LENGTH(column) - LENGTH(REPLACE(column, ' ', ''))+1) 
	FROM tables

Counts the number of spaces in the content for all the rows in a column for a database table. Adding 1 gives the total number of words.

计算数据库表的列中所有行的内容中的空格数。 加1得出单词总数。

If you have other MySQL queries that you find useful, please feel free to add them in the comments below!

如果您发现其他有用MySQL查询，请随时在下面的注释中添加它们！

翻译自: https://thenewcode.com/366/Handy-MySQL-Queries-Random-Rows-Sequence-Gaps-Replace-Content-amp-Word-Count

mysql 替换序列