mysql - 在连接条件中添加通配符

Question

如何将通配符 % 添加到“specifications.sn”？

SELECT `products2`.`type`, 
       `products2`.`sn`, 
       `products2`.`lap`, 
       `specifications`.`year` 
FROM `products2`
INNER JOIN `specifications` 
      ON `products2`.`sn` LIKE `specifications`.`sn`
WHERE `products2`.`id` = ? LIMIT 1

编辑:

添加当前查询

• 删除反引号

• 添加了您的 CONCAT

  SELECT products2.type, 
  products2.sn, 
  products2.lap, 
  specifications.year 
  FROM products2
  INNER JOIN specifications 
  ON products2.sn LIKE CONCAT('%', specifications.sn, '%')
  WHERE products2.id = ? LIMIT 1
  

替代查询 - 与上面的代码相同的错误

• 删除反引号
• 删除了 INNER JOIN

• 在 WHERE 语句中添加 LIKE

  SELECT products2.type, 
  products2.sn, 
  products2.lap, 
  specifications.year
  FROM products2, 
  specifications
  WHERE products2.id = ? AND products2.sn LIKE '%' + specifications.sn + '%' LIMIT 1
  

编辑2

PHP代码

if ($stmt = $mysqli->prepare('SELECT products2.type, 
products2.sn, 
products2.lap, 
specifications.year 
FROM products2
INNER JOIN specifications 
ON products2.sn LIKE CONCAT('%', specifications.sn, '%')
WHERE products2.id = ? LIMIT 1')) { 

$stmt->bind_param('i', $id);
$id = $_GET['id'];
$stmt->execute();
$stmt->bind_result($type, $sn, $lap, $year);
$stmt->fetch();

echo $type . '<br/>';
echo $sn . '<br/>';
echo $lap . '<br/>';
echo $year . '<br/>';

$stmt->close();

} else {
echo $mysqli->error;
}

Answer 1

SELECT `products2`.`type`,
       `products2`.`sn`,
        `products2`.`lap`, `specifications`.`year`
FROM `products2`
INNER JOIN `specifications`
      ON `products2`.`sn` LIKE 
         CONCAT(`specifications`.`sn`, '%')
WHERE `products2`.`id` = ? LIMIT 1

请注意以下事项:

• 这对于参加表演来说是非常无效的
• CONCAT(NULL, 'ADADSA') 返回 NULL，因此如果您认为规范，则必须处理这个特殊用例.sn 可能为 NULL。

干杯。