flush-acf.php

<?php

/**
 * Flush ACF
 *
 * Gives you insight and lets you delete unused postmeta rows created by
 * Advanced Custom Fields 5 (ACF PRO). Tested with v5.1.4.
 *
 * Author: Tim Brugman, @Brugman, tim@timbrugman.com
 * License: MIT (http://opensource.org/licenses/MIT)
 */

// set state depending on $_POST
////////////////////////////////////////

$flushacf_exec_state = 'no_exec';
if ( isset( $_POST['flushacf_exec'] ) )
{
	$flushacf_exec_val = true;
	if ( !isset( $_POST['flushacf_exec'] ) || $_POST['flushacf_exec']!=1 ) $flushacf_exec_val = false;
	if ( !isset( $_POST['flushacf_eula'] ) || $_POST['flushacf_eula']!=1 ) $flushacf_exec_val = false;

	$flushacf_exec_state = 'no_eula';
	if ( $flushacf_exec_val ) $flushacf_exec_state = 'exec';
}

// functions
////////////////////////////////////////

if ( !function_exists( 'sort_by_name' ) )
{
	function sort_by_name( $a, $b )
	{
		return strcmp( $a['name'], $b['name'] );
	}
}

if ( !function_exists( 'display_as_table' ) )
{
	function display_as_table( $l1_items )
	{
		echo '<table class="table">';
		echo '<tr>';
		if ( !empty( $l1_items ) ) foreach ( $l1_items[0] as $l2_key => $l2_value ) echo '<th>'.$l2_key.'</th>';
		echo '</tr>';
		if ( !empty( $l1_items ) ) foreach ( $l1_items as $l1_item )
		{
			echo '<tr>';
			foreach ( $l1_item as $l2_item ) echo '<td>'.$l2_item.'</td>';
			echo '</tr>';
		}
		echo '</table>';
	}
}

if ( !function_exists( 'get_acf_meta_field_ids' ) )
{
	function get_acf_meta_field_ids()
	{
		global $wpdb;
		return $wpdb->get_results( 'SELECT * FROM `'.$wpdb->prefix.'postmeta` WHERE `meta_value` LIKE "field_%"', 'ARRAY_A' );
	}
}

if ( !function_exists( 'get_acf_meta_field_ids_distinct' ) )
{
	function get_acf_meta_field_ids_distinct()
	{
		global $wpdb;
		return $wpdb->get_results( 'SELECT DISTINCT `meta_value` FROM `'.$wpdb->prefix.'postmeta` WHERE `meta_value` LIKE "field_%"', 'ARRAY_A' );
	}
}

if ( !function_exists( 'get_names_for_ids' ) )
{
	function get_names_for_ids( $field_ids )
	{
		// create array
		$result = array();
		// if we have ids, loop them
		if ( !empty( $field_ids ) ) foreach ( $field_ids as $field_id )
		{
			// get each field's posts
			$fields_data = get_posts(array(
				'posts_per_page' => -1,
				'post_type'      => 'acf-field',
				'name'           => $field_id['meta_value'],
			));
			// if we have posts, add them to the results
			if ( !empty( $fields_data ) ) foreach ( $fields_data as $field_data )
			{
				$result[] = array(
					'id' => $field_id['meta_value'],
					'name' => $field_data->post_excerpt,
				);
			}
		}
		// return
		return $result;
	}
}

if ( !function_exists( 'get_inactive_names' ) )
{
	function get_inactive_names( $active_names )
	{
		// if we have no data dont even bother
		if ( empty( $active_names ) ) return array();
		// global
		global $wpdb;
		// build sql query
		$sql_inactive = 'SELECT DISTINCT `meta_key` FROM `'.$wpdb->prefix.'postmeta` WHERE';
		// loop items
		foreach ( $active_names as $active_name )
		{
			// build sql query
			$sql_inactive .= ' ( `meta_value` = "'.$active_name['id'].'" AND ( `meta_key` NOT IN ( "'.$active_name['name'].'", "_'.$active_name['name'].'" ) ) ) OR';
		}
		// build sql query - clean up ending
		$sql_inactive = substr( $sql_inactive, 0, -3 );
		// execute and return
		return $wpdb->get_results( $sql_inactive, 'ARRAY_A' );
	}
}

if ( !function_exists( 'get_rows_inactive_names' ) )
{
	function get_rows_inactive_names( $inactive_names )
	{
		// if we have no data dont even bother
		if ( empty( $inactive_names ) ) return array();
		// global
		global $wpdb;
		// build sql query
		$sql_rows_inactive = 'SELECT * FROM `'.$wpdb->prefix.'postmeta` WHERE `meta_key` IN (';
		// loop items
		foreach ( $inactive_names as $inactive_name )
		{
			// build sql query
			$sql_rows_inactive .= ' "'.$inactive_name['meta_key'].'", "'.substr( $inactive_name['meta_key'], 1 ).'",';
		}
		// build sql query - clean up ending
		$sql_rows_inactive = substr( $sql_rows_inactive, 0, -1 ).' )';
		// execute and return
		return $wpdb->get_results( $sql_rows_inactive, 'ARRAY_A' );
	}
}

if ( !function_exists( 'delete_rows_inactive_names' ) )
{
	function delete_rows_inactive_names( $death_rows )
	{
		// if we have no data dont even bother
		if ( empty( $death_rows ) ) return;
		// global
		global $wpdb;
		// build sql query
		$sql_delete_rows = 'DELETE FROM `'.$wpdb->prefix.'postmeta` WHERE `meta_id` IN (';
		// loop items
		foreach ( $death_rows as $death_row )
		{
			// build sql query
			$sql_delete_rows .= ' '.$death_row['meta_id'].',';
		}
		// build sql query
		$sql_delete_rows = substr( $sql_delete_rows, 0, -1 );
		$sql_delete_rows .= ' )';
		// execute
		$wpdb->get_results( $sql_delete_rows, 'ARRAY_A' );
	}
}

// runtime!
////////////////////////////////////////

$field_ids = get_acf_meta_field_ids_distinct();

$active_names = get_names_for_ids( $field_ids );

$inactive_names = get_inactive_names( $active_names );

$rows_inactive_names = get_rows_inactive_names( $inactive_names );

usort( $active_names, 'sort_by_name' );

// HTML
////////////////////////////////////////

?>
<!DOCTYPE html>
<html>
<head>

	<!-- meta -->
	<meta charset="utf-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">

	<!-- link css -->
	<link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.1/css/bootstrap.min.css">
	<link rel="stylesheet" href="//bootswatch.com/slate/bootstrap.min.css">
	<link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/font-awesome/4.2.0/css/font-awesome.min.css">

	<style>
	body { padding-top: 100px; padding-bottom: 300px; }
	h1 { font-size: 20px; }
	.col { margin-top: 30px; }
	</style>

</head>
<body>

<div class="container">

	<h1>Flush ACF</h1>

<?php if ( $flushacf_exec_state=='exec' ): ?>

	<div class="col col-xs-12">

		<p>Flushing...</p>

<?php delete_rows_inactive_names( $rows_inactive_names ); ?>

		<p>Done.</p>

		<p>Please <a href="<?=$_SERVER['REQUEST_URI'];?>">click here</a> to check out your fresh database.</p>

	</div>

<?php endif; // state ?>

<?php if ( $flushacf_exec_state=='no_eula' ): ?>

	<div class="col col-xs-12 alert alert-info">

		<p>You did not check the EULA checkbox. Flush ACF did not flush.</p>

	</div>

<?php endif; // state ?>

<?php if ( $flushacf_exec_state!='exec' ): ?>

	<div class="col col-xs-6">

		<p>Your <?=count( $active_names );?> active field names are:</p>

		<ul>

<?php foreach ( $active_names as $active_name ): ?>

			<li><?=$active_name['name'];?></li>

<?php endforeach; ?>

		</ul>

	</div>

	<div class="col col-xs-6">

		<p>Your <?=count( $inactive_names );?> inactive field names are:</p>

		<ul>

<?php foreach ( $inactive_names as $inactive_name ): ?>

			<li><?=substr( $inactive_name['meta_key'], 1 );?></li>

<?php endforeach; ?>

		</ul>

	</div>

	<div class="col col-xs-12">

		<p>When you flush ACF fields we will delete these <strong><?=count( $rows_inactive_names );?></strong> rows that are linked to your inactive field names:</p>

<?php display_as_table( $rows_inactive_names ); ?>

	</div>

	<div class="col col-xs-12">

		<form class="" action="<?=$_SERVER['REQUEST_URI'];?>" method="post" role="form">

			<div class="form-group">

				<label>Flush database</label>

				<div class="checkbox">

					<label for="flushacf_eula">

						<input type="checkbox" id="flushacf_eula" name="flushacf_eula" value="1">
						I acknoledge the use of this code is solely my responsability.

					</label>

				</div>

			</div>

			<div class="form-group">

				<button type="submit" class="btn btn-primary" name="flushacf_exec" value="1">Flush database</button>

			</div>

		</form>

	</div>

<?php endif; // state ?>

</div>

<!-- jQuery -->
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<!-- Bootstrap -->
<script src="//maxcdn.bootstrapcdn.com/bootstrap/3.3.1/js/bootstrap.min.js"></script>

</body>
</html>

I lost data from one page but that wasn't a problem. Nicely done, thanks.
Used on ACF 5.1.8

Completely wiped out most of the ACF fields - even active ones. Avoid using this script. Did not work as intended.

did you ever find a safe way?