中文(繁體)
中文(繁體)
mysqli_stmt::bind_param的第二個參數用於指定綁定變量的類型，它可以是以下幾個字符：
i ：整型（integer）
d ：雙精度浮動類型（double）
s ：字符串（string）
b ：二進制數據（blob）
最常見的類型轉換錯誤之一就是傳遞給bind_param的變量類型與預期的類型不一致。例如，如果你在SQL語句中期望一個整數（ i ），但實際上傳遞的是一個字符串類型的變量，MySQL會將該字符串轉換為0或者發生錯誤。
錯誤示例：
<span><span><span class="hljs-variable">$stmt</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">prepare</span></span><span>(</span><span><span class="hljs-string">"SELECT * FROM users WHERE age = ?"</span></span><span>);
</span><span><span class="hljs-variable">$age</span></span><span> = </span><span><span class="hljs-string">"25"</span></span><span>; </span><span><span class="hljs-comment">// 字符串類型</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"i"</span></span><span>, </span><span><span class="hljs-variable">$age</span></span><span>); </span><span><span class="hljs-comment">// 綁定類型錯誤，應該是整數（i）</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span>解決方法：
確保綁定的變量類型與SQL查詢語句的預期類型一致。如果變量類型不匹配，可以先進行類型轉換，再進行綁定。
<span><span><span class="hljs-variable">$age</span></span><span> = </span><span><span class="hljs-number">25</span></span><span>; </span><span><span class="hljs-comment">// 確保是整數類型</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"i"</span></span><span>, </span><span><span class="hljs-variable">$age</span></span><span>); </span><span><span class="hljs-comment">// 綁定整型</span></span><span>
</span></span>mysqli_stmt::bind_param函數在處理NULL值時比較特殊。 PHP中的NULL必須顯式地綁定，而不能直接傳遞給bind_param 。否則，它會導致執行失敗或不正確的行為。
錯誤示例：
<span><span><span class="hljs-variable">$stmt</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">prepare</span></span><span>(</span><span><span class="hljs-string">"INSERT INTO users (name, age) VALUES (?, ?)"</span></span><span>);
</span><span><span class="hljs-variable">$name</span></span><span> = </span><span><span class="hljs-string">"John"</span></span><span>;
</span><span><span class="hljs-variable">$age</span></span><span> = </span><span><span class="hljs-literal">null</span></span><span>; </span><span><span class="hljs-comment">// null值</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"si"</span></span><span>, </span><span><span class="hljs-variable">$name</span></span><span>, </span><span><span class="hljs-variable">$age</span></span><span>); </span><span><span class="hljs-comment">// 錯誤，無法直接綁定NULL</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span>解決方法：
在綁定NULL值時， bind_param不支持直接綁定NULL ，因此需要將其轉換為適當的格式或使用bind_param的不同版本。最常見的方法是使用mysqli_stmt::send_long_data ，但這對於NULL值的處理較為繁瑣。
<span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$age</span></span><span> === </span><span><span class="hljs-literal">null</span></span><span>) {
</span><span><span class="hljs-variable">$age</span></span><span> = </span><span><span class="hljs-number">0</span></span><span>; </span><span><span class="hljs-comment">// 或者根據實際需求設置一個默認值</span></span><span>
}
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"si"</span></span><span>, </span><span><span class="hljs-variable">$name</span></span><span>, </span><span><span class="hljs-variable">$age</span></span><span>); </span><span><span class="hljs-comment">// 綁定默認值</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span>有時我們會遇到將數字當做字符串傳遞，或者相反。雖然MySQL在某些情況下會自動進行類型轉換，但這仍然可能導致性能問題或不可預料的行為。
錯誤示例：
<span><span><span class="hljs-variable">$stmt</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">prepare</span></span><span>(</span><span><span class="hljs-string">"SELECT * FROM products WHERE price = ?"</span></span><span>);
</span><span><span class="hljs-variable">$price</span></span><span> = </span><span><span class="hljs-number">199.99</span></span><span>; </span><span><span class="hljs-comment">// 價格是浮動數值</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"s"</span></span><span>, </span><span><span class="hljs-variable">$price</span></span><span>); </span><span><span class="hljs-comment">// 錯誤，應該使用d（double）類型</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span>解決方法：
如果你傳遞的是浮動數值，務必使用d （double）類型，而不是s （string）。這不僅確保正確的數據類型傳遞，也提高了查詢的效率。
<span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"d"</span></span><span>, </span><span><span class="hljs-variable">$price</span></span><span>); </span><span><span class="hljs-comment">// 綁定浮動數值</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span>bind_param僅支持簡單的標量類型（整數、浮動數值、字符串、二進制數據）。如果傳遞的是數組或對象， bind_param會失敗。因此，在傳遞複雜數據類型之前，需要對數據進行預處理。
錯誤示例：
<span><span><span class="hljs-variable">$stmt</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">prepare</span></span><span>(</span><span><span class="hljs-string">"INSERT INTO users (preferences) VALUES (?)"</span></span><span>);
</span><span><span class="hljs-variable">$preferences</span></span><span> = [</span><span><span class="hljs-string">"color"</span></span><span> => </span><span><span class="hljs-string">"blue"</span></span><span>, </span><span><span class="hljs-string">"size"</span></span><span> => </span><span><span class="hljs-string">"L"</span></span><span>]; </span><span><span class="hljs-comment">// 数组類型</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"s"</span></span><span>, </span><span><span class="hljs-variable">$preferences</span></span><span>); </span><span><span class="hljs-comment">// 錯誤，數組不能直接綁定</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span>解決方法：
在傳遞數組或對象之前，通常需要將其序列化為JSON字符串或其他格式。
<span><span><span class="hljs-variable">$preferences</span></span><span> = </span><span><span class="hljs-title function_ invoke__">json_encode</span></span><span>([</span><span><span class="hljs-string">"color"</span></span><span> => </span><span><span class="hljs-string">"blue"</span></span><span>, </span><span><span class="hljs-string">"size"</span></span><span> => </span><span><span class="hljs-string">"L"</span></span><span>]);
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"s"</span></span><span>, </span><span><span class="hljs-variable">$preferences</span></span><span>); </span><span><span class="hljs-comment">// 綁定JSON字符串</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span>如果綁定的數字超出了指定類型的最大範圍，也會引發類型轉換錯誤。對於整型（ i ），它的範圍通常是-2,147,483,648到2,147,483,647 。對於浮動數值（ d ），範圍要大得多，但仍有上限。
錯誤示例：
<span><span><span class="hljs-variable">$stmt</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">prepare</span></span><span>(</span><span><span class="hljs-string">"INSERT INTO large_numbers (value) VALUES (?)"</span></span><span>);
</span><span><span class="hljs-variable">$value</span></span><span> = </span><span><span class="hljs-number">1000000000000</span></span><span>; </span><span><span class="hljs-comment">// 過大的數字</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"i"</span></span><span>, </span><span><span class="hljs-variable">$value</span></span><span>); </span><span><span class="hljs-comment">// 錯誤，數字超出了整型範圍</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span>解決方法：
如果傳遞的數字超出了整型範圍，可以使用浮動類型（ d ）來避免溢出，或者將其轉換為合適的範圍。
<span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"d"</span></span><span>, </span><span><span class="hljs-variable">$value</span></span><span>); </span><span><span class="hljs-comment">// 使用雙精度浮動數值</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-></span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();
</span></span>
相關標籤:
mysqli_stmt
