日常开发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;