MySQL has many features for searching a database for specific text. I am using an example where I am looking for a certain email address in an old WordPress database dump.
select * from wp_makronusers where user_email rlike "aol"; |
This returns all matches containing that text.
Find all users in the database that have an ID less than 10.
mysql> select * from wp_makronusers where ID < 10; |
Find a row that matches a search term.
select * from wp_makronusers where user_nicename = "trottepew"; |
Get a listing of all rows in a database table.
mysql> describe wp_makronposts; +-----------------------+---------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------------+------+-----+---------------------+----------------+ | ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | post_author | bigint(20) unsigned | NO | MUL | 0 | | | post_date | datetime | NO | | 0000-00-00 00:00:00 | | | post_date_gmt | datetime | NO | | 0000-00-00 00:00:00 | | | post_content | longtext | NO | | NULL | | | post_title | text | NO | | NULL | | | post_excerpt | text | NO | | NULL | | | post_status | varchar(20) | NO | | publish | | | comment_status | varchar(20) | NO | | open | | | ping_status | varchar(20) | NO | | open | | | post_password | varchar(20) | NO | | | | | post_name | varchar(200) | NO | MUL | | | | to_ping | text | NO | | NULL | | | pinged | text | NO | | NULL | | | post_modified | datetime | NO | | 0000-00-00 00:00:00 | | | post_modified_gmt | datetime | NO | | 0000-00-00 00:00:00 | | | post_content_filtered | text | NO | | NULL | | | post_parent | bigint(20) unsigned | NO | MUL | 0 | | | guid | varchar(255) | NO | | | | | menu_order | int(11) | NO | | 0 | | | post_type | varchar(20) | NO | MUL | post | | | post_mime_type | varchar(100) | NO | | | | | comment_count | bigint(20) | NO | | 0 | | +-----------------------+---------------------+------+-----+---------------------+----------------+ 23 rows in set (0.00 sec) |
Search the database table and return a count of all matches for a text search.
mysql> select count(*) from wp_makronposts where post_content rlike "gentoo"; +----------+ | count(*) | +----------+ | 45 | +----------+ 1 row in set (0.05 sec) |
Return a count of all matches for a certain regexp.
mysql> SELECT count(*) post_content FROM wp_makronposts WHERE post_content REGEXP 'linux kernel 2.35'; +--------------+ | post_content | +--------------+ | 6 | +--------------+ 1 row in set (0.09 sec) |
Using the MySQL SELECT statement only shows records of a particular type.
More MySQL tips for viewing data in a database and listing the columns in a table.
Export a database from MySQL when it is using the secure file privilege setting.
Import a WordPress database dump into MySQL easily.
https://securitronlinux.com/debian-testing/import-a-wordpress-database-dump-into-mysql-easily/.
How to view the Admin account in the WordPress database using MySQL and a database dump.
How to list the indexes in a MySQL database table.
https://securitronlinux.com/bejiitaswrath/how-to-list-the-indexes-in-a-mysql-database-table/.