日常开发MySql语句记录整理

1.更新替换数据库指定字段中的换行符（char(10)）和回车符（char(13)）

UPDATE foreign_schools SET country = REPLACE ( REPLACE ( country, CHAR ( 10 ), ” ), CHAR ( 13 ), ” )

2.搜索查询中orwhere和where的使用

$schools = $foreignSchool->when($request->has(“year”), function ($query) use ($request) {
return $query->where(“year”, $request->year);
})->when($request->has(“name”), function ($query) use ($request) {
return $query->where(function ($query) use ($request) {
$query->where(“name”, “like”, “%{$request->name}%”)->orWhere(“cname”, “like”, “%{$request->name}%”);
});
})->orderBy(“year”, “desc”)->paginate($request->number ?? 20);

3.整理MySql自增ID

set @num = 0;
update world_colleges set id = @num := @num +1;