1. Print the SQL statement executed by the program, the result is as follows. As shown in Figure 1
PHP
$builder = ThemeAsset::select('updated_at', 'asset_key')
->where('theme_id', '=', $this->dbViewStorage->getThemeId())
->whereRaw('LOWER(asset_key) regexp ?', [strtolower($this->targetToRegex($target))]);
$query = str_replace(array('?'), array('\'%s\''), $builder->toSql());
$query = vsprintf($query, $builder->getBindings());
dump($query);
exit;
Plaintext
"select `updated_at`, `asset_key` from `theme_asset` where `theme_id` = 'theme' and LOWER(asset_key) regexp '^(assets|apps\.+\.+\assets)'"
2. Print the results of the SQL statement executed by the program, the count is 79. Array elements start from 0. As shown in Figure 2
3. Copy the printed SQL and execute it in MySQL 5.7. The count is 81. As shown in Figure 3
4. Decide to remove the ? in whereRaw and adjust it to raw SQL. Print the results of the SQL statement executed by the program, the count is 81. As shown in Figure 4
PHP
$builder = ThemeAsset::select('updated_at', 'asset_key')
->where('theme_id', '=', $this->dbViewStorage->getThemeId())
->whereRaw('LOWER(asset_key) regexp \'^(assets|apps\.+\.+\assets)\'')
5. Keep the ? in whereRaw, need to use addslashes to escape \. Print the results of the SQL statement executed by the program, the count is 81. This meets expectations.
PHP
$assets = ThemeAsset::select('updated_at', 'asset_key')
->where('theme_id', '=', $this->dbViewStorage->getThemeId())
->whereRaw('LOWER(asset_key) regexp ?', [strtolower(addslashes($this->targetToRegex($target)))])
->get();