Print SQL in Laravel 6, copy to MySQL 5.7, the execution result is inconsistent with the program execution result
1. Print the SQL statement executed by the program, and the result is as follows. as shown in Figure 1
Figure 1
$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;
"select `updated_at`, `asset_key` from `theme_asset` where `theme_id` = 'theme' and LOWER(asset_key) regexp '^(assets|apps\.+\.+\assets)'"
2. The number of SQL statements executed by the print program is 79. Array elements start with 0. as shown in Figure 2
Figure 2
3. Print the SQL and copy it to MySQL 5.7 to execute. Its number is 81. as shown in Figure 3
Figure 3
4. Decide to cancel the ? in whereraw and adjust to native sql. The result of the SQL statement executed by the program is 81. as shown in Figure 4
Figure 4
$builder = ThemeAsset::select('updated_at', 'asset_key')
->where('theme_id', '=', $this->dbViewStorage->getThemeId())
->whereRaw('LOWER(asset_key) regexp \'^(assets|apps\.+\.+\assets)\'')
5. To keep the ? in whereraw, you need to use addslashes to escape \. The result of the SQL statement executed by the program is 81. in line with expectations.
$assets = ThemeAsset::select('updated_at', 'asset_key')
->where('theme_id', '=', $this->dbViewStorage->getThemeId())
->whereRaw('LOWER(asset_key) regexp ?', [strtolower(addslashes($this->targetToRegex($target)))])
->get();
![在程序运行时报错：syntax error, unexpected ',', expecting ']'](https://www.shuijingwanwq.com/wp-content/uploads/2023/07/1-11-520x245.png)
