求用于计算OS Type和OS Language的SQL Server查询语句

云原生阿岚

2026-5-25

Hey there! I totally get where you're coming from—SQL doesn't have a one-size-fits-all built-in way to grab OS Type and OS Language, since this info lives in different places depending on your setup. Let's walk through the common scenarios and how to handle each with SQL:

Scenario 1: You already store OS details in your business tables

If you have a table (like user_devices or system_metadata) that explicitly tracks OS type and language, it's straightforward. For example, to pull a breakdown of counts per OS and language:

SELECT 
  os_type,
  os_language,
  COUNT(*) AS total_records
FROM user_devices
GROUP BY os_type, os_language
ORDER BY total_records DESC;

Scenario 2: You need OS info for the database server itself

Different database systems expose server OS details through system variables or functions:

SQL Server: Extract from the version string and use collation to infer language context:

SELECT
  -- Get OS name from the server version string
  SUBSTRING(@@VERSION, CHARINDEX('Windows', @@VERSION), 
            CHARINDEX(' ', @@VERSION, CHARINDEX('Windows', @@VERSION)) - CHARINDEX('Windows', @@VERSION)) AS os_type,
  -- Collation setting reflects the server's default language context
  SERVERPROPERTY('Collation') AS os_language_context;

MySQL: Use system variables to fetch OS context and language settings:

SELECT
  @@version_comment AS os_type, -- Often includes OS details like "Win64" or "Linux"
  @@lc_time_names AS os_language; -- Shows language code like 'en_US' or 'fr_FR'

PostgreSQL: Pull from the version function and locale configuration:

SELECT
  version() AS os_type, -- Includes OS details e.g., "PostgreSQL 15.3 on x86_64-pc-linux-gnu..."
  current_setting('lc_messages') AS os_language; -- e.g., 'en_US.UTF-8'

Scenario 3: You need to parse OS info from client data (like User-Agent strings)

If you store web request data with user_agent or accept_language fields, you can parse these with string functions. Here's a basic example for MySQL:

SELECT
  CASE
    WHEN user_agent LIKE '%Windows NT%' THEN 'Windows'
    WHEN user_agent LIKE '%Mac OS X%' THEN 'macOS'
    WHEN user_agent LIKE '%Linux%' THEN 'Linux'
    WHEN user_agent LIKE '%Android%' THEN 'Android'
    WHEN user_agent LIKE '%iPhone%' OR user_agent LIKE '%iPad%' THEN 'iOS'
    ELSE 'Unknown OS'
  END AS os_type,
  -- Extract primary language from the Accept-Language header
  SUBSTRING_INDEX(SUBSTRING_INDEX(accept_language, ',', 1), ';', 1) AS os_language
FROM web_requests;

Note: For more accurate parsing, you might need regex functions (like REGEXP_SUBSTR in PostgreSQL/SQL Server) or a custom function, but this basic CASE approach works for most common cases.

The key takeaway: You first need to identify where your OS type and language data resides—whether it's in your own tables, the database server's system info, or client request metadata. Once you know that, the SQL approach falls into place!

内容的提问来源于stack exchange，提问作者Prashanth Edige